Set @mytime=Now();

Select @mytime as StartProcess;

USE `acc-001`;

CREATE TABLE `accbook` (
  `acckey` int(11) unsigned NOT NULL auto_increment,
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `category` enum('BS','PL') NOT NULL default 'BS',
  `acctype` enum('SALES','SALESADJ','COS','OI','EXP','TAX','EOI','EOE','APP','FA','OA','CA','CL','EQ','LTL') NOT NULL default 'SALES',
  `reftype1` enum('APL','BANK','CASH','CREDITOR','CREDITORCONTROL','DEBTOR','DEBTORCONTROL','FAD','MANUFACTURING','NONE') NOT NULL default 'NONE',
  `reftype2` enum('BALANCESTOCK','CLOSINGSTOCK','NONE','OPENINGSTOCK') NOT NULL default 'NONE',
  `drcr` enum('DR','CR') NOT NULL default 'DR',
  `level` tinyint(3) unsigned NOT NULL default '0',
  `rootkey` int(11) unsigned NOT NULL default '0',
  `parentkey` int(11) unsigned NOT NULL default '0',
  `linkkey` varchar(250) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `cashflowtype` enum('NONE','OPERATING','INVESTING','FINANCING') NOT NULL default 'NONE',
  `rate` decimal(20,2) NOT NULL default '1.00',
  `curcode` varchar(20) NOT NULL default '',
  `costcentrecode` varchar(20) NOT NULL default '',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`acckey`),
  UNIQUE KEY `iabacccode` (`acccode`),
  KEY `iabdescr` (`descr`),
  KEY `iabcategory` (`category`),
  KEY `iabacctypereftype12` (`acctype`,`reftype1`,`reftype2`),
  KEY `iabreftype12` (`reftype1`,`reftype2`),
  KEY `iabdrcr` (`drcr`),
  KEY `iablevel` (`level`),
  KEY `iabrootkey` (`rootkey`),
  KEY `iabparentkey` (`parentkey`),
  KEY `iablinkkey` (`linkkey`),
  KEY `iabinactive` (`inactive`),
  KEY `iabcurcode` (`curcode`),
  KEY `iabcostcentrecode` (`costcentrecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `accbookob` (
  `acccode` varchar(20) NOT NULL default '',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `debit` decimal(20,2) NOT NULL default '0.00',
  `credit` decimal(20,2) NOT NULL default '0.00',
  `curcode` varchar(20) NOT NULL default '',
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `fdebit` decimal(20,2) NOT NULL default '0.00',
  `fcredit` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  USING BTREE (`acccode`,`departmentcode`,`jobcode`),
  KEY `iaobdepartmentcode` (`departmentcode`),
  KEY `iaobjobcode` (`jobcode`),
  KEY `iaobcurcode` (`curcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `accbooktitle` (
  `acccode` varchar(10) NOT NULL default '',
  `lan` varchar(10) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `pos` mediumint NOT NULL default '0',
  PRIMARY KEY  (`acccode`,`lan`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `accbudget` (
  `accbudgetyear` smallint(5) unsigned NOT NULL default '0',
  `accbudgetmonth` enum('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC') NOT NULL default 'JAN',
  `acccode` varchar(20) NOT NULL default '',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `budgetvalue` decimal(20,2) NOT NULL default '0.00',
  `budgetdate` date default NULL,
  PRIMARY KEY  USING BTREE (`accbudgetyear`,`accbudgetmonth`,`acccode`,`departmentcode`,`jobcode`),
  KEY `abgyeardepartmentcode` (`accbudgetyear`,`departmentcode`),
  KEY `abgacccode` (`acccode`),
  KEY `abgdepartmentcode` (`departmentcode`),
  KEY `abgbudgetdate` (`budgetdate`),
  KEY `abgyearjobcode` (`accbudgetyear`,`jobcode`),
  KEY `abgjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `acctrans` (
  `acctranskey` int(11) unsigned NOT NULL auto_increment,
  `acctransdate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `descr2` varchar(200) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `acccode2` varchar(20) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `fdebit` decimal(20,2) NOT NULL default '0.00',
  `fcredit` decimal(20,2) NOT NULL default '0.00',
  `debit` decimal(20,2) NOT NULL default '0.00',
  `credit` decimal(20,2) NOT NULL default '0.00',
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `docid` enum('1','2','3','4','5','6','7','8','9','10','11') NOT NULL default '1',
  `docrefno` varchar(20) NOT NULL default '',
  `docrefno2` varchar(30) NOT NULL default '',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `bcurcode` varchar(20) NOT NULL default '',
  `bcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `bdebit` decimal(20,2) NOT NULL default '0.00',
  `bcredit` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`acctranskey`),
  KEY `iatdate` (`acctransdate`),
  KEY `iatacccode` (`acccode`),
  KEY `iatacccode2` (`acccode2`),
  KEY `iatdoctypebookkeypos` (`doctype`,`dockey`,`docpos`,`docid`),
  KEY `iatdepartmentcode` (`departmentcode`),
  KEY `iatcurcode` (`curcode`),
  KEY `iatdocrefno` (`docrefno`),
  KEY `iatjobcode` (`jobcode`),
  KEY `iatbcurcode` (`bcurcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `agentrole` (
  `agentrolekey` int(11) unsigned NOT NULL auto_increment,
  `agentrolecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`agentrolekey`),
  UNIQUE KEY `iarcode` (`agentrolecode`),
  KEY `iarinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `agentsalestarget` (
  `agentsalestargetyear` smallint(5) unsigned NOT NULL default '0',
  `agentsalestargetmonth` enum('JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC') NOT NULL default 'JAN',
  `agentcode` varchar(20) NOT NULL default '',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `salestargetdate` date default NULL,
  `salestargetvalue` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  USING BTREE (`agentsalestargetyear`,`agentsalestargetmonth`,`agentcode`,`departmentcode`,`jobcode`),
  KEY `iastgyeardepartmentcode` (`agentsalestargetyear`,`departmentcode`),
  KEY `iastgagentcode` (`agentcode`),
  KEY `iastgdepartmentcode` (`departmentcode`),
  KEY `iastgyearjobcode` (`agentsalestargetyear`,`jobcode`),
  KEY `iastgjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE  `alternativeitem` (
  `stockkey` int(11) unsigned NOT NULL,
  `stockcode` varchar(30) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `alternativestockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`stockkey`,`alternativestockcode`),
  UNIQUE KEY `iaistockalternativecode` (`stockcode`,`alternativestockcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `annuallongerperiodadjustment` (
  `annuallongerperiodadjustmentkey` int(11) unsigned NOT NULL auto_increment,
  `annuallongerperiodadjustmentcode` varchar(20) NOT NULL default '',
  `annuallongerperiodadjustmentdate` date default NULL,
  `taxperiodstartdate` date default NULL,
  `taxperiodenddate` date default NULL,
  `dmr` decimal(20,2) NOT NULL default '0.00',
  `dmrstatus` enum('None','Qualify', 'No Qualify') NOT NULL default 'None',
  `t` decimal(20,2) NOT NULL default '0.00',
  `e` decimal(20,2) NOT NULL default '0.00',
  `o` decimal(20,2) NOT NULL default '0.00',
  `irr` decimal(20,2) NOT NULL default '0.00',
  `localtotalitctxreeligible` decimal(20,2) NOT NULL default '0.00',
  `localtotalitctxn43eligible` decimal(20,2) NOT NULL default '0.00',
  `localtotalitceligible` decimal(20,2) NOT NULL default '0.00',
  `localtotalitctxreclaimed` decimal(20,2) NOT NULL default '0.00',
  `localtotalitctxn43claimed` decimal(20,2) NOT NULL default '0.00',
  `localtotalitcclaimed` decimal(20,2) NOT NULL default '0.00',
  `localtotaladjustment` decimal(20,2) NOT NULL default '0.00',
  `gsttypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `debitacccode` varchar(20) NOT NULL default '',
  `creditacccode` varchar(20) NOT NULL default '',
  `journalkey` int(11) unsigned default NULL,
  PRIMARY KEY  (`annuallongerperiodadjustmentkey`),
  UNIQUE KEY `ipeacode` (`annuallongerperiodadjustmentcode`),
  KEY `ialpadate` (`annuallongerperiodadjustmentdate`),
  KEY `ialpataxperiodstartdate` (`taxperiodstartdate`),
  KEY `ialpataxperiodenddate` (`taxperiodenddate`),
  KEY `ialpagsttypecode` (`gsttypecode`),
  KEY `ialpacancelled` (`cancelled`),
  KEY `ialpadebitacccode` (`debitacccode`),
  KEY `ialpacreditacccode` (`creditacccode`),
  KEY `ialpajournalkey` (`journalkey`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `annuallongerperiodadjustmenttxre` (
  `annuallongerperiodadjustmentkey` int(11) unsigned NOT NULL auto_increment,
  `annuallongerperiodadjustmentcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `gsttransdate` date default NULL,
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `docid` int(11) unsigned NOT NULL default '0',
  `gstdescr` varchar(200) NOT NULL default '',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('Supply','Purchase') NOT NULL default 'Supply',
  `taxpercentage` decimal(20,2) default NULL,
  `taxableacccode` varchar(20) NOT NULL default '',
  `taxableaccname` varchar(200) NOT NULL default '',
  `taxacccode` varchar(20) NOT NULL default '',
  `taxaccname` varchar(200) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `regno` varchar(30) NOT NULL default '',
  `msiccode` varchar(20) NOT NULL default '',
  `gst03_item16` enum('Y','N') NOT NULL default 'N',
  `irr` decimal(20,2) NOT NULL default '0.00',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `gstclaimed` decimal(20,2) NOT NULL default '0.00',
  `gstexpenses` decimal(20,2) NOT NULL default '0.00',
  `localtaxableamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxamount` decimal(20,2) NOT NULL default '0.00',
  `localgstclaimed` decimal(20,2) NOT NULL default '0.00',
  `localgstexpenses` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  USING BTREE (`annuallongerperiodadjustmentkey`,`pos`,`doccode`),
  UNIQUE KEY `ialpatxrecodepos` (`annuallongerperiodadjustmentcode`,`pos`),
  KEY `ialpatxredoctypekeypos` (`doctype`,`dockey`,`docpos`),
  KEY `ialpatxredoctypecodepos` (`doctype`,`doccode`,`docpos`),
  KEY `ialpatxredoctypekeyid` (`doctype`,`dockey`,`docid`),
  KEY `ialpatxredoctypecodeid` (`doctype`,`doccode`,`docid`),
  KEY `ialpatxrecurcode` (`curcode`),
  KEY `ialpatxredepartmentcode` (`departmentcode`),
  KEY `ialpatxrejobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `annuallongerperiodadjustmenttxn43` (
  `annuallongerperiodadjustmentkey` int(11) unsigned NOT NULL auto_increment,
  `annuallongerperiodadjustmentcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `gsttransdate` date default NULL,
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `docid` int(11) unsigned NOT NULL default '0',
  `gstdescr` varchar(200) NOT NULL default '',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('Supply','Purchase') NOT NULL default 'Supply',
  `taxpercentage` decimal(20,2) default NULL,
  `taxableacccode` varchar(20) NOT NULL default '',
  `taxableaccname` varchar(200) NOT NULL default '',
  `taxacccode` varchar(20) NOT NULL default '',
  `taxaccname` varchar(200) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `regno` varchar(30) NOT NULL default '',
  `msiccode` varchar(20) NOT NULL default '',
  `gst03_item16` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxableamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  USING BTREE (`annuallongerperiodadjustmentkey`,`pos`,`doccode`),
  UNIQUE KEY `ialpatxn43codepos` (`annuallongerperiodadjustmentcode`,`pos`),
  KEY `ialpatxn43doctypekeypos` (`doctype`,`dockey`,`docpos`),
  KEY `ialpatxn43doctypecodepos` (`doctype`,`doccode`,`docpos`),
  KEY `ialpatxn43doctypekeyid` (`doctype`,`dockey`,`docid`),
  KEY `ialpatxn43doctypecodeid` (`doctype`,`doccode`,`docid`),
  KEY `ialpatxn43curcode` (`curcode`),
  KEY `ialpatxn43departmentcode` (`departmentcode`),
  KEY `ialpatxn43jobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `appointment` (
  `appointmentkey` int(11) unsigned NOT NULL auto_increment,
  `id` varchar(40) NOT NULL default '',
  `parentid` varchar(40) NOT NULL default '',
  `type` int(10) unsigned NOT NULL default '0',
  `start` datetime default NULL,
  `finish` datetime default NULL,
  `options` int(10) unsigned NOT NULL default '0',
  `subject` varchar(200) NOT NULL default '',
  `recurrenceindex` int(10) unsigned NOT NULL default '0',
  `recurrenceinfo` mediumblob,
  `resourceid` mediumblob,
  `areacode` varchar(20) NOT NULL default '',
  `message` mediumtext,
  `reminderdate` datetime default NULL,
  `reminderminutes` int(10) unsigned NOT NULL default '0',
  `state` int(10) unsigned NOT NULL default '0',
  `labelcolor` int(10) unsigned NOT NULL default '0',
  `actualstart` datetime default NULL,
  `actualfinish` datetime default NULL,
  `appointmenttypecode` varchar(20) NOT NULL default '',
  `appointmentprioritycode` varchar(20) NOT NULL default '',
  `salesleadkey` int(10) unsigned NOT NULL default '0',
  `servicenotekey` int(10) unsigned NOT NULL default '0',
  `debtorcode` varchar(20) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `contactperson` varchar(200) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `servicepersoncode` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `syncidfield` varchar(255) NOT NULL default '',
  `module` VARCHAR(50) NOT NULL DEFAULT '',
  `done` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`appointmentkey`),
  KEY `isfastaffcode` (`staffcode`),
  KEY `isfadebtorcode` (`debtorcode`),
  KEY `isfaareacode` (`areacode`),
  KEY `isfasalesleadkey` (`salesleadkey`),
  KEY `isfatypecode` (`appointmenttypecode`),
  KEY `isfaprioritycode` (`appointmentprioritycode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `appointmenttype` (
  `appointmenttypekey` int(11) unsigned NOT NULL auto_increment,
  `appointmenttypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`appointmenttypekey`),
  UNIQUE KEY `isfatcode` (`appointmenttypecode`),
  KEY `isfatinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `appointmentpriority` (
  `appointmentprioritykey` int(11) unsigned NOT NULL auto_increment,
  `appointmentprioritycode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`appointmentprioritykey`),
  UNIQUE KEY `isfapcode` (`appointmentprioritycode`),
  KEY `isfapinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `approvalinfo` (
  `approvalinfokey` int(11) unsigned NOT NULL auto_increment,
  `doctype` varchar(5) NOT NULL default '',
  `doccode` varchar(20) NOT NULL default '',
  `loginusercode` varchar(20) NOT NULL default '',
  `approvalusercode` varchar(20) NOT NULL default '',
  `supervisorcode` varchar(20) NOT NULL default '',
  `approvaldate` datetime NOT NULL default '0000-00-00 00:00:00',
  `remarks` varchar(250) default '',
  `remarks2` varchar(250) default '',
  PRIMARY KEY  (`approvalinfokey`),
  KEY `iapidoctypecode` (`doctype`,`doccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `arapgroup` (
  `groupkey` int(11) unsigned NOT NULL auto_increment,
  `groupcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `debtorcode` varchar(20) NOT NULL,
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`groupkey`),
  UNIQUE KEY `igcode` (`groupcode`),
  KEY `iginactive` USING BTREE (`inactive`),
  KEY `igdebtorcode` (`debtorcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `area` (
  `areakey` int(11) unsigned NOT NULL auto_increment,
  `areacode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`areakey`),
  UNIQUE KEY `iacode` (`areacode`),
  KEY `iainactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `asset` (
  `assetkey` int(11) unsigned NOT NULL auto_increment,
  `assetcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `purchasedate` date default NULL,
  `purchaseprice` decimal(20,2) NOT NULL default '0.00',
  `manufacturer` varchar(100) NOT NULL default '',
  `serialno` varchar(100) NOT NULL default '',
  `modelno` varchar(100) NOT NULL default '',
  `billno` varchar(50) NOT NULL default '',
  `invoiceno` varchar(50) NOT NULL default '',
  `refno1` varchar(50) NOT NULL default '',
  `refno2` varchar(50) NOT NULL default '',
  `refno3` varchar(50) NOT NULL default '',
  `refno4` varchar(50) NOT NULL default '',
  `refno5` varchar(50) NOT NULL default '',
  `refno6` varchar(50) NOT NULL default '',
  `refno7` varchar(50) NOT NULL default '',
  `refno8` varchar(50) NOT NULL default '',
  `refno9` varchar(50) NOT NULL default '',
  `refno10` varchar(50) NOT NULL default '',
  `refno11` varchar(50) NOT NULL default '',
  `refno12` varchar(50) NOT NULL default '',
  `creditorcode` varchar(20) NOT NULL default '',
  `faacccode` varchar(20) NOT NULL default '',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `assetlocationcode` varchar(100) NOT NULL default '',
  `note` mediumtext,
  `photo` mediumblob,
  `assettypecode` varchar(20) NOT NULL default '',
  `useexistingdepreciation` enum('Y','N') NOT NULL default 'N',
  `lastnetbookvalue` decimal(20,2) NOT NULL default '0.00',
  `lastdepreciationdate` date default NULL,
  `adjustmenttype` enum('No Adjustment','In Average','In Month After Last Depreciation Date','In Final Month of Asset Span Life') NOT NULL default 'No Adjustment',
  `accumdepbf` decimal(20,2) NOT NULL default '0.00',
  `assetcacode` varchar(20) NOT NULL default '',
  `hirepurchase` enum('Y','N') NOT NULL default 'N',
  `disposal` enum('Y','N') NOT NULL default 'N',
  `disposaldate` date default NULL,
  `disposalprice` decimal(20,2) NOT NULL default '0.00',
  `accmulateddepreciation` decimal(20,2) NOT NULL default '0.00',
  `netbookvalue` decimal(20,2) NOT NULL default '0.00',
  `maxqualifyexpenditure` decimal(20,2) NOT NULL default '0.00',
  `warrantydate` date default NULL,
  `roadtaxexpiredate` date default NULL,
  `roadtaxfee` decimal(20,2) NOT NULL default '0.00',
  `leasingno` varchar(100) NOT NULL default '',
  `leasingexpiredate` date default NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`assetkey`),
  UNIQUE KEY `iascode` (`assetcode`),
  KEY `iaspurchasedate` (`purchasedate`),
  KEY `iasserialno` (`serialno`),
  KEY `iasbillno` (`billno`),
  KEY `iasinvoiceno` (`invoiceno`),
  KEY `iascreditorcode` (`creditorcode`),
  KEY `iasfaacccode` (`faacccode`),
  KEY `iasdepartmentcode` (`departmentcode`),
  KEY `iasassettypecode` (`assettypecode`),
  KEY `iasassetcacode` (`assetcacode`),
  KEY `iashirepurchase` (`hirepurchase`),
  KEY `iasdisposal` (`disposal`),
  KEY `iasdisposaldate` (`disposaldate`),
  KEY `iaswarrantydate` (`warrantydate`),
  KEY `iasroadtaxexpiredate` (`roadtaxexpiredate`),
  KEY `iasleasingexpiredate` (`leasingexpiredate`),
  KEY `iasjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `assettransfer` (
  `assettransferkey` int(11) unsigned NOT NULL auto_increment,
  `assettransfercode` varchar(20) NOT NULL default '',
  `assettransferdate` date default NULL,
  `staffcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `refno2` varchar(20) NOT NULL default '',
  `refno3` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `fromdepartmentcode` varchar(20) NOT NULL default '',
  `todepartmentcode` varchar(20) NOT NULL default '',
  `fromjobcode` varchar(20) NOT NULL default '',
  `tojobcode` varchar(20) NOT NULL default '',
  `fromassetlocationcode` varchar(20) NOT NULL default '',
  `toassetlocationcode` varchar(20) NOT NULL default '',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `itemformatkey` int(11) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`assettransferkey`),
  UNIQUE KEY `istfcode` (`assettransfercode`),
  KEY `iastfdate` (`assettransferdate`),
  KEY `iastfstaffcode` (`staffcode`),
  KEY `iastfrefno` (`refno`),
  KEY `iastfrefno2` (`refno2`),
  KEY `iastfrefno3` (`refno3`),
  KEY `iastffromdepartmentcode` (`fromdepartmentcode`),
  KEY `iastftodepartmentcode` (`todepartmentcode`),
  KEY `iastffromassetlocationcode` (`fromassetlocationcode`),
  KEY `iastftoassetlocationcode` (`toassetlocationcode`),
  KEY `iastfcancelled` (`cancelled`),
  KEY `iastffromjobcode` (`fromjobcode`),
  KEY `iastftojobcode` (`tojobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `assettransferid` (
  `assettransferkey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`assettransferkey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `assettransferitem` (
  `assettransferkey` int(11) unsigned NOT NULL default '0',
  `assettransfercode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `assetcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `amount` decimal(20,2) default NULL,
  PRIMARY KEY  (`assettransferkey`,`pos`),
  UNIQUE KEY `iastfikeyid` (`assettransferkey`,`id`),
  KEY `iastficode` (`assettransfercode`),
  KEY `iastfiassetcode` (`assetcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `assettype` (
  `assettypekey` int(11) unsigned NOT NULL auto_increment,
  `assettypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `depreciationtype` enum('NONE','STRAIGHTLINE','REDUCINGBALANCE') NOT NULL default 'STRAIGHTLINE',
  `salvagevalue` decimal(20,2) NOT NULL default '0.00',
  `caltype` enum('RATE','LIFE') NOT NULL default 'RATE',
  `calvalue` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`assettypekey`),
  UNIQUE KEY `iastcode` (`assettypecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `assetca` (
  `assetcakey` int(11) unsigned NOT NULL auto_increment,
  `assetcacode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `caia` decimal(20,2) NOT NULL default '0.00',
  `defaultcaaa` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`assetcakey`),
  UNIQUE KEY `iascacode` (`assetcacode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE  `assetcayear` (
  `assetcacode` varchar(20) NOT NULL default '',
  `cayear` smallint(5) unsigned NOT NULL default '0',
  `caaa` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`assetcacode`,`cayear`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE  `assethp` (
  `assetcode` varchar(20) NOT NULL default '',
  `hpacccode` varchar(20) NOT NULL default '',
  `commencementdate` date default NULL,
  `noofinstalment` smallint(5) unsigned NOT NULL default '0',
  `monthlyinstalment` decimal(20,2) NOT NULL default '0.00',
  `finalinstalment` decimal(20,2) NOT NULL default '0.00',
  `cashprice` decimal(20,2) NOT NULL default '0.00',
  `totalincidental` decimal(20,2) NOT NULL default '0.00',
  `totalincidentalca` decimal(20,2) NOT NULL default '0.00',
  `deposit` decimal(20,2) NOT NULL default '0.00',
  `totalhpprincipal` decimal(20,2) NOT NULL default '0.00',
  `totalhpinterest` decimal(20,2) NOT NULL default '0.00',
  `totalhp` decimal(20,2) NOT NULL default '0.00',
  `hpinteresttype` enum('STRAIGHTLINE','SUMOFDIGIT') NOT NULL default 'SUMOFDIGIT',
  PRIMARY KEY  (`assetcode`),
  KEY `iashpacccode` (`hpacccode`),
  KEY `iashpcommencementdate` (`commencementdate`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE  `assethpincidental` (
  `assetcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `descr` varchar(200) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `caallowed` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`assetcode`,`pos`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE  `assethpyear` (
  `assetcode` varchar(20) NOT NULL default '',
  `hpyear` smallint(5) unsigned NOT NULL default '0',
  `startinstalment` smallint(5) unsigned NOT NULL default '0',
  `endinstalment` smallint(5) unsigned NOT NULL default '0',
  `noofinstalment` smallint(5) unsigned NOT NULL default '0',
  `hpprincipal` decimal(20,2) NOT NULL default '0.00',
  `hpinterest` decimal(20,2) NOT NULL default '0.00',
  `sumhp` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`assetcode`,`hpyear`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `assetlocation` (
  `assetlocationkey` int(11) unsigned NOT NULL auto_increment,
  `assetlocationcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `note` mediumtext,
  `level` tinyint(3) unsigned NOT NULL default '0',
  `rootkey` int(11) unsigned NOT NULL default '0',
  `parentkey` int(11) unsigned NOT NULL default '0',
  `linkkey` varchar(250) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`assetlocationkey`),
  UNIQUE KEY `iastlcode` (`assetlocationcode`),
  KEY `iastllevel` (`level`),
  KEY `iastlrootkey` (`rootkey`),
  KEY `iastlparentkey` (`parentkey`),
  KEY `iastllinkkey` (`linkkey`),
  KEY `iastlinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `atmasterdetaildatadef` (
  `formname` varchar(200) NOT NULL default '',
  `masterfieldnamelist` mediumblob,
  `masterdisplaynamelist` mediumblob,
  `detaildisplaynamelist` mediumblob,
  PRIMARY KEY  (`formname`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `attachment` (
  `attachmentkey` int(11) unsigned NOT NULL auto_increment,
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `filecode` varchar(20) NOT NULL default '',
  `filename` varchar(250) NOT NULL default '',
  `filedate` date default NULL,
  `originalfilename` varchar(250) NOT NULL default '',
  `fileattachment` mediumblob,
  `descr` varchar(200) NOT NULL default '',
  `dbfile` enum('Y','N') NOT NULL default 'N',
  `pathname` mediumtext,
  PRIMARY KEY  (`attachmentkey`),
  KEY `iattfilecode` (`filecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `audittrail` (
  `formcodekey` int(10) unsigned NOT NULL default '0',
  `formname` varchar(100) NOT NULL default '',
  `version` int(10) unsigned NOT NULL,
  `formcode` varchar(50) default '',
  `actiondatetime` datetime NOT NULL default '0000-00-00 00:00:00',
  `action` varchar(20) NOT NULL default '',
  `usercode` varchar(20) NOT NULL default '',
  `remarks` varchar(250) default '',
  `master` enum('Y','N') NOT NULL default 'N',
  `detail` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`formcodekey`,`formname`,`version`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `audittraildetail` (
  `formcodekey` int(10) unsigned NOT NULL default '0',
  `formname` varchar(100) NOT NULL default '',
  `version` int(10) unsigned NOT NULL default '0',
  `masterdata` blob,
  `detaildata` blob,
  PRIMARY KEY  (`formcodekey`,`formname`,`version`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `bank` (
  `bankkey` int(11) unsigned NOT NULL auto_increment,
  `bankcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `branch` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`bankkey`),
  UNIQUE KEY `ibcode` (`bankcode`),
  KEY `ibinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `bankpaymentlog` (
  `bankpaymentlogkey` bigint(20) unsigned NOT NULL auto_increment,
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docdate` date default NULL,
  `docpos` int(11) unsigned NOT NULL default '0',
  `generatebankpaymentdate` datetime default NULL,
  `reason`  varchar(200) NOT NULL default '',
  `note` mediumtext,
  PRIMARY KEY  (`bankpaymentlogkey`),
  KEY `ibpldoctype` (`doctype`),
  KEY `ibpldockey` (`dockey`),
  KEY `ibpldoccode` (`doccode`),
  KEY `ibpldocpos` (`docpos`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `bankrecon` (
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `docid` enum('1','2') NOT NULL default '1',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `descr2` varchar(200) NOT NULL default '',
  `debit` decimal(20,2) unsigned NOT NULL default '0.00',
  `credit` decimal(20,2) unsigned NOT NULL default '0.00',
  `docdate` date default NULL,
  `docrefno` varchar(20) NOT NULL default '',
  `docrefno2` varchar(30) NOT NULL default '',
  `cleared` enum('Y','N') NOT NULL default 'N',
  `datecleared` date default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `fdebit` decimal(20,2) NOT NULL default '0.00',
  `fcredit` decimal(20,2) NOT NULL default '0.00',
  `acccode2` varchar(20) NOT NULL default '',
  `bcurcode` varchar(20) NOT NULL default '',
  `bcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `bdebit` decimal(20,2) NOT NULL default '0.00',
  `bcredit` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`doctype`,`dockey`,`docpos`,`docid`),
  KEY `ibracccode` (`acccode`),
  KEY `ibrdocdate` (`docdate`),
  KEY `ibrcleared` (`cleared`),
  KEY `ibrdepartmentcode` (`departmentcode`),
  KEY `ibracccode2` (`acccode2`),
  KEY `ibrcurcode` (`curcode`),
  KEY `ibrjobcode` (`jobcode`),
  KEY `ibrbcurcode` (`bcurcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `bankreconob` (
  `bankreconobkey` int(11) unsigned NOT NULL auto_increment,
  `doccode` varchar(20) NOT NULL default '',
  `docdate` date default NULL,
  `acccode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `descr` varchar(200) NOT NULL default '',
  `docrefno` varchar(20) NOT NULL default '',
  `debit` decimal(20,2) unsigned NOT NULL default '0.00',
  `credit` decimal(20,2) unsigned NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `bcurcode` varchar(20) NOT NULL default '',
  `bcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `bdebit` decimal(20,2) NOT NULL default '0.00',
  `bcredit` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`bankreconobkey`),
  KEY `ibrobacccodedatepos` (`acccode`,`docdate`,`pos`),
  KEY `ibtobdepartmentcode` (`departmentcode`),
  KEY `ibtobjobcode` (`jobcode`),
  KEY `ibrobbcurcode` (`bcurcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `bill` (
  `billkey` int(11) unsigned NOT NULL auto_increment,
  `billcode` varchar(20) NOT NULL default '',
  `billdate` date default NULL,
  `creditorcode` varchar(20) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `multi` enum('Y','N') NOT NULL default 'N',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `duedate` date default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `fromdoctype` varchar(5) NOT NULL default 'BILL',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `totaltaxableamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `totalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `totallocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  PRIMARY KEY  (`billkey`),
  UNIQUE KEY `ibillcode` (`billcode`),
  KEY `ibilldate` (`billdate`),
  KEY `ibillcreditorcode` (`creditorcode`),
  KEY `ibillacccode` (`acccode`),
  KEY `ibillrefno` (`refno`),
  KEY `ibillmulti` (`multi`),
  KEY `ibilltermcode` (`termcode`),
  KEY `ibillstaffcode` (`staffcode`),
  KEY `ibilldepartmentcode` (`departmentcode`),
  KEY `ibillfromdoctype` (`fromdoctype`),
  KEY `ibillareacode` (`areacode`),
  KEY `ibillcurcode` (`curcode`),
  KEY `ibilljobcode` (`jobcode`),
  KEY `ibillcancelled` (`cancelled`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `billmulti` (
  `billkey` int(11) unsigned NOT NULL default '0',
  `billcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `subamount` decimal(20,2) NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `localsubamount` decimal(20,2) NOT NULL default '0.00',
  `amountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `localamountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxpercentage` decimal(20,2) default NULL,
  `taxacccode` varchar(20) NOT NULL default '',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxrefno` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`billkey`,`pos`),
  KEY `ibillmacccode` (`acccode`),
  KEY `ibillmcode` (`billcode`),
  KEY `ibillmdepartmentcode` (`departmentcode`),
  KEY `ibillmjobcode` (`jobcode`),
  KEY `ibmgsttypecode` (`gsttypecode`),
  KEY `ibmtaxacccode` (`taxacccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `billob` (
  `billkey` int(11) unsigned NOT NULL auto_increment,
  `billcode` varchar(20) NOT NULL default '',
  `billdate` date default NULL,
  `creditorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `duedate` date default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`billkey`),
  UNIQUE KEY `ibillobcode` (`billcode`),
  KEY `ibillobdate` (`billdate`),
  KEY `ibillobcreditorcode` (`creditorcode`),
  KEY `ibillobrefno` (`refno`),
  KEY `ibillobtermcode` (`termcode`),
  KEY `ibillobstaffcode` (`staffcode`),
  KEY `ibillobdepartmentcode` (`departmentcode`),
  KEY `ibillobareacode` (`areacode`),
  KEY `ibillobcurcode` (`curcode`),
  KEY `ibillobjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `cancelledpresetreceiptno` (
  `cancelledpresetreceiptnokey` int(11) unsigned NOT NULL auto_increment,
  `receiptno` varchar(20) NOT NULL default '',
  `cancelleddate` date default NULL,
  `cancelledremark` varchar(200) NOT NULL default '',
  `usercode` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`cancelledpresetreceiptnokey`),
  KEY `icprreceiptno` (`receiptno`),
  KEY `icprusercode` (`usercode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `cashbooktype` (
  `cashbooktypekey` int(11) unsigned NOT NULL auto_increment,
  `cashbooktypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`cashbooktypekey`),
  UNIQUE KEY `icbtcode` (`cashbooktypecode`),
  KEY `icsbtinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `cashier` (
  `cashierkey` int(11) unsigned NOT NULL auto_increment,
  `cashiercode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `pwd` blob NOT NULL,
  `cashamount` decimal(20,2) NOT NULL default '0.00',
  `allowdiscount` enum('Y','N') NOT NULL default 'N',
  `allowchangeunitprice` enum('Y','N') NOT NULL default 'N',
  `allowchangecustomer` enum('Y','N') NOT NULL default 'N',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `allowchangeuom` enum('Y','N') NOT NULL default 'N',
  `allowcancelreceipt` enum('Y','N') NOT NULL default 'N',
  `allowdeleterow` enum('Y','N') NOT NULL default 'N',
  `allowreprintreceipt` enum('Y','N') NOT NULL default 'N',
  `allowvoidreceipt` enum('Y','N') NOT NULL default 'N',
  `maxdiscount` decimal(20,2) NOT NULL default '0.00',
  `itemmaxdiscount` decimal(20,2) NOT NULL default '0.00',
  `allowdepositrefund` enum('Y','N') NOT NULL default 'N',
  `allowfreeitem` enum('Y','N') NOT NULL default 'N',
  `addmemberdebitcardvalue` varchar(20) NOT NULL default '',
  `addmemberdebitcardadjvalue` varchar(20) NOT NULL default '',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`cashierkey`),
  UNIQUE KEY `icshrcode` (`cashiercode`),
  KEY `icshrinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `cashierinout` (
  `cashierinoutkey` int(11) unsigned NOT NULL auto_increment,
  `cashierindatetime` datetime default NULL,
  `cashieroutdatetime` datetime default NULL,
  `countercode` varchar(20) NOT NULL default '',
  `cashiercode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`cashierinoutkey`),
  KEY `ichiocountercode` (`countercode`),
  KEY `ichiocashiercode` (`cashiercode`),
  KEY `ichiocashierindatetime` (`cashierindatetime`),
  KEY `ichiocashieroutdatetime` (`cashieroutdatetime`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `cashinout` (
  `cashinoutkey` int(11) unsigned NOT NULL auto_increment,
  `cashinoutdatetime` datetime default NULL,
  `countercode` varchar(20) NOT NULL default '',
  `cashiercode` varchar(20) NOT NULL default '',
  `supervisorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `cashtype` enum('IN','OUT') NOT NULL default 'IN',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `cashinoutclientdatetime` datetime default NULL,
  `issystem` enum('Y','N') NOT NULL default 'N',
  `remark` varchar(30) NOT NULL,
  PRIMARY KEY  (`cashinoutkey`),
  KEY `iciocountercode` (`countercode`),
  KEY `iciocashiercode` (`cashiercode`),
  KEY `iciosupervisorcode` (`supervisorcode`),
  KEY `iciocashtype` (`cashtype`),
  KEY `iciocashinoutdatetime` (`cashinoutdatetime`),
  KEY `icioremark` (`remark`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `cashreceiptdeposit` (
  `cashreceiptdepositkey` int(11) unsigned NOT NULL auto_increment,
  `cashreceiptdepositcode` varchar(20) NOT NULL default '',
  `cashreceiptdepositdate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `descr2` varchar(200) NOT NULL default '',
  `note` mediumtext,
  `refund` enum('Y','N') NOT NULL default 'N',
  `refundapproval` varchar(20) NOT NULL default '',
  `refundamount` decimal(20,2) NOT NULL default '0.00',
  `refunddate` date default NULL,
  `refunddescr` varchar(200) NOT NULL default '',
  `refunddescr2` varchar(200) NOT NULL default '',
  `refundcountercode` varchar(20) NOT NULL default '',
  `refundcashiercode` varchar(20) NOT NULL default '',
  `fullpaid` enum('Y','N') NOT NULL default 'N',
  `fullpaidcountercode` varchar(20) NOT NULL default '',
  `fullpaidcashiercode` varchar(20) NOT NULL default '',
  `countercode` varchar(20) NOT NULL default '',
  `cashiercode` varchar(20) NOT NULL default '',
  `cashreceive` decimal(20,2) NOT NULL default '0.00',
  `creditcard1amount` decimal(20,2) NOT NULL default '0.00',
  `creditcard2amount` decimal(20,2) NOT NULL default '0.00',
  `creditcardtype1` varchar(20) NOT NULL default '',
  `creditcardno1` varchar(50) NOT NULL default '',
  `creditcardnameoncard1` varchar(200) NOT NULL default '',
  `creditcardexpireddate1` varchar(10) NOT NULL default '',
  `creditcardapprovalcode1` varchar(50) NOT NULL default '',
  `creditcardbankname1` varchar(100) NOT NULL,
  `creditcardchargepercent1` decimal(20,2) NOT NULL default '0.00',
  `creditcardchargeamount1` decimal(20,2) NOT NULL default '0.00',
  `creditcardtype2` varchar(20) NOT NULL default '',
  `creditcardno2` varchar(50) NOT NULL default '',
  `creditcardnameoncard2` varchar(200) NOT NULL default '',
  `creditcardexpireddate2` varchar(10) NOT NULL default '',
  `creditcardapprovalcode2` varchar(50) NOT NULL default '',
  `creditcardbankname2` varchar(100) NOT NULL,
  `creditcardchargepercent2` decimal(20,2) NOT NULL default '0.00',
  `creditcardchargeamount2` decimal(20,2) NOT NULL default '0.00',
  `debitcardamount` decimal(20,2) NOT NULL default '0.00',
  `debitcarddescr` varchar(200) NOT NULL default '',
  `debitcardrefno` varchar(20) NOT NULL default '',
  `chequeamount` decimal(20,2) NOT NULL default '0.00',
  `chequedescr` varchar(200) NOT NULL default '',
  `chequeno` varchar(20) NOT NULL,
  `clientcreated` datetime default NULL,
  `refundclientcreated` datetime default NULL,
  `fullpaidclientcreated` datetime default NULL,
  `creditcardpaymenttype1` varchar(20) NOT NULL default '',
  `creditcardpaymenttype2` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`cashreceiptdepositkey`),
  UNIQUE KEY `icsdcode` (`cashreceiptdepositcode`),
  KEY `icsddate` (`cashreceiptdepositdate`),
  KEY `icsdrefund` (`refund`),
  KEY `icsdfullpaid` (`fullpaid`),
  KEY `icsdcountercode` (`countercode`),
  KEY `icsdcashiercode` (`cashiercode`),
  KEY `icsdrefundcountercode` (`refundcountercode`),
  KEY `icsdrefundcashiercode` (`refundcashiercode`),
  KEY `icsdfullpaidcountercode` (`fullpaidcountercode`),
  KEY `icsdfullpaidcashiercode` (`fullpaidcashiercode`),
  KEY `icsdclientcreated` (`clientcreated`),
  KEY `icsdrefundclientcreated` (`refundclientcreated`),
  KEY `icsdfullpaidclientcreated` (`fullpaidclientcreated`),
  KEY `icscreditcardpaymenttype1` (`creditcardpaymenttype1`),
  KEY `icscreditcardpaymenttype2` (`creditcardpaymenttype2`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `cashtype` (
  `cashtypekey` int(11) unsigned NOT NULL auto_increment,
  `cashtypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `cashtypetype` enum('Note','Coin') NOT NULL default 'Note',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`cashtypekey`),
  KEY `ictcashtypecode` (`cashtypecode`),
  KEY `ictcashtype` (`cashtypetype`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `cashtypeinout` (
  `cashtypeinout` int(11) unsigned NOT NULL auto_increment,
  `cashinoutkey` int(11) unsigned NOT NULL default '0',
  `cashtypecode` varchar(20) NOT NULL default '',
  `qty` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`cashtypeinout`),
  KEY `ictiocashtypecode` (`cashtypecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `category` (
  `categorykey` int(11) unsigned NOT NULL auto_increment,
  `categorycode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`categorykey`),
  UNIQUE KEY `iccode` (`categorycode`),
  KEY `icinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `chartfilter` (
  `chartfilterkey` int(11) unsigned NOT NULL auto_increment,
  `chartcode` varchar(20) NOT NULL default '',
  `fieldcode` varchar(50) NOT NULL default '',
  `descr` varchar(100) NOT NULL default '',
  `type` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`chartfilterkey`),
  UNIQUE KEY `idchartfilter` (`chartcode`,`fieldcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `chartmanager` (
  `chartkey` int(11) unsigned NOT NULL auto_increment,
  `chartcode` varchar(20) NOT NULL default '',
  `system` enum('Y','N') NOT NULL default 'N',
  `descr` varchar(100) NOT NULL default '',
  `beforecommand` mediumtext,
  `selectcommand` mediumtext,
  `aftercommand` mediumtext,
  `chartformat` mediumblob,
  `moduletype` int(11) NOT NULL default '1',
  `pos` int(11) NOT NULL default '1',
  `formaname` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`chartkey`),
  UNIQUE KEY `idchartcodesystem` (`chartcode`,`system`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `collectionplan` (
  `collectionplankey` int(11) unsigned NOT NULL auto_increment,
  `collectionplancode` varchar(20) NOT NULL default '',
  `collectionplantype` enum('Day','Week','Month', 'Year') NOT NULL default 'Day',
  `collectionplantypevalue` int(11) unsigned NOT NULL default '0',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`collectionplankey`),
  UNIQUE KEY `icpccode` (`collectionplancode`),
  KEY `icpcinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE  `collectionplantrans` (
  `collectionplantranskey` int(11) unsigned NOT NULL auto_increment,
  `dockey` int(11) unsigned NOT NULL,
  `doctype` varchar(5) NOT NULL default '',
  `collectionplandate` date default NULL,
  `collectionplanamount` decimal(20,2) NOT NULL default '0.00',
  `fromdockey` int(11) unsigned NOT NULL,
  `fromdoctype` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`collectionplantranskey`),
  UNIQUE KEY `ismcode` (`dockey`,`doctype`, `collectionplandate`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `comment` (
  `commentkey` int(11) unsigned NOT NULL auto_increment,
  `commentcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`commentkey`),
  UNIQUE KEY `icmtcode` (`commentcode`),
  KEY `icmtinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `commissionbyamount` (
  `commissionbyamountkey` int(11) unsigned NOT NULL auto_increment,
  `commissionbyamountcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`commissionbyamountkey`),
  UNIQUE KEY `icmbaode` (`commissionbyamountcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `commissionbyamountitem` (
  `commissionbyamountcode` varchar(20) NOT NULL default '',
  `collectionamount` decimal(20,2) NOT NULL default '0.00',
  `commissionpercent` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`commissionbyamountcode`,`collectionamount`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `commissionbyperiod` (
  `commissionbyperiodkey` int(11) unsigned NOT NULL auto_increment,
  `commissionbyperiodcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`commissionbyperiodkey`),
  UNIQUE KEY `icmbaode` (`commissionbyperiodcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `commissionbyperioditem` (
  `commissionbyperiodcode` varchar(20) NOT NULL default '',
  `collectionperiod` decimal(20,2) NOT NULL default '0.00',
  `commissionpercent` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`commissionbyperiodcode`,`collectionperiod`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `commissionbystkgroupperiod` (
  `commissionbystkgroupperiodkey` int(11) unsigned NOT NULL auto_increment,
  `commissionbystkgroupperiodcode` varchar(20) NOT NULL default '',
  `stockgroupcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`commissionbystkgroupperiodkey`),
  UNIQUE KEY `icmbgpcode` (`commissionbystkgroupperiodcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `commissionbystkgroupperioditem` (
  `commissionbystkgroupperiodcode` varchar(20) NOT NULL default '',
  `collectionperiod` decimal(20,2) NOT NULL default '0.00',
  `commissionpercent` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`commissionbystkgroupperiodcode`,`collectionperiod`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `companybank` (
  `bankcode` varchar(20) NOT NULL default '',
  `accountno` varchar(30) NOT NULL default '',
  `glacccode` varchar(20) NOT NULL default '',
  `branch` varchar(200) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`bankcode`,`pos`),
  UNIQUE KEY `icbglacccode` (`glacccode`),
  KEY `icnaccountno` (`accountno`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `companyinfo` (
  `companyname` varchar(200) NOT NULL default '',
  `companyname2` varchar(200) NOT NULL default '',
  `regno` varchar(30) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `phone2` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `fax2` varchar(30) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `email2` varchar(200) NOT NULL default '',
  `homepage` varchar(200) NOT NULL default '',
  `contactperson` varchar(200) NOT NULL default '',
  `businessnature` varchar(200) NOT NULL default '',
  `curcode` varchar(20) NOT NULL default '',
  `cursymbol` varchar(5) NOT NULL default '',
  `curname` varchar(100) NOT NULL default '',
  `curshortname` varchar(10) NOT NULL default '',
  `background` mediumblob,
  `logo` mediumblob,
  `locationmap` mediumblob,
  `reportheader` mediumblob,
  `shortreportheader` mediumblob,
  `subcompanyname` varchar(200) NOT NULL default '',
  `subcompanyname2` varchar(200) NOT NULL default '',
  `subregno` varchar(30) NOT NULL default '',
  `subshortreportheader` mediumblob,
  `authorisedcapital` decimal(20,2) NOT NULL default '0.00',
  `paidupcapital` decimal(20,2) NOT NULL default '0.00',
  `nominalshare` decimal(20,2) NOT NULL default '0.00',
  `businessaddress1` varchar(200) NOT NULL default '',
  `businessaddress2` varchar(200) NOT NULL default '',
  `businessaddress3` varchar(200) NOT NULL default '',
  `businessaddress4` varchar(200) NOT NULL default '',
  `registeredaddress1` varchar(200) NOT NULL default '',
  `registeredaddress2` varchar(200) NOT NULL default '',
  `registeredaddress3` varchar(200) NOT NULL default '',
  `registeredaddress4` varchar(200) NOT NULL default '',
  `director` mediumblob,
  `directorshare` mediumblob,
  `secretary` mediumblob,
  `managername` varchar(200) NOT NULL default '',
  `manageric` varchar(30) NOT NULL default '',
  `manageraddress1` varchar(200) NOT NULL default '',
  `manageraddress2` varchar(200) NOT NULL default '',
  `manageraddress3` varchar(200) NOT NULL default '',
  `manageraddress4` varchar(200) NOT NULL default '',
  `managerphone` varchar(30) NOT NULL default '',
  `managerfax` varchar(30) NOT NULL default '',
  `auditorname` varchar(200) NOT NULL default '',
  `auditoric` varchar(30) NOT NULL default '',
  `auditorlicenseno` varchar(30) NOT NULL default '',
  `auditoraddress1` varchar(200) NOT NULL default '',
  `auditoraddress2` varchar(200) NOT NULL default '',
  `auditoraddress3` varchar(200) NOT NULL default '',
  `auditoraddress4` varchar(200) NOT NULL default '',
  `auditorphone` varchar(30) NOT NULL default '',
  `auditorfax` varchar(30) NOT NULL default '',
  `shareholder` mediumblob,
  `shareholdershare` mediumblob,
  `principalactivities` mediumblob,
  `incorporationdate` date default NULL,
  `financialyearend` date default NULL,
  `indexno` varchar(200) NOT NULL default '',
  `gstregno` varchar(30) NOT NULL default '',
  `gstdisplayname` varchar(30) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `postalcode` char(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  `gststartdate` date default NULL,
  `gstregisterdate` date default NULL,
  `msiccode` varchar(20) NOT NULL default '',
  `gsttaxableperiod` int(11) unsigned default '3',
  KEY `icicity` (`city`),
  KEY `icistate` (`state`),
  KEY `icicountrycode` (`countrycode`),
  KEY `icimsiccode` (`msiccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `configuretools` (
  `configuretoolskey` int(11) unsigned NOT NULL auto_increment,
  `descr` varchar(200) NOT NULL default '',
  `toolspath` mediumtext,
  `computername` mediumtext,
  `done` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`configuretoolskey`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `consignmenttype` (
  `consignmenttypekey` int(11) unsigned NOT NULL auto_increment,
  `consignmenttypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`consignmenttypekey`),
  UNIQUE KEY `ictcode` (`consignmenttypecode`),
  KEY `ictpinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `costcentre` (
  `costcentrekey` int(11) unsigned NOT NULL auto_increment,
  `costcentrecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`costcentrekey`),
  UNIQUE KEY `icccode` (`costcentrecode`),
  KEY `iccinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `counter` (
  `counterkey` int(11) unsigned NOT NULL auto_increment,
  `countercode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `debtorcode` varchar(20) NOT NULL default '',
  `docnokey` int(11) unsigned default NULL,
  `sinvdocnokey` int(11) unsigned default NULL,
  `departmentcode` varchar(20) NOT NULL,
  `jobcode` varchar(20) NOT NULL default '',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`counterkey`),
  UNIQUE KEY `icoucode` (`countercode`),
  KEY `icouinactive` (`inactive`),
  KEY `icoustocklocationcode` (`stocklocationcode`),
  KEY `icoudebtorcode` (`debtorcode`),
  KEY `icoudepartmentcode` (`departmentcode`),
  KEY `icoujobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `country` (
  `countrykey` int(11) unsigned NOT NULL auto_increment,
  `countrycode` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`countrykey`),
  UNIQUE KEY `iccode` (`countrycode`),
  KEY `icinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditcardtype` (
  `creditcardtypekey` int(11) unsigned NOT NULL auto_increment,
  `creditcardtypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `creditchargepercentageforpos` decimal(20,2) NOT NULL default '0.00',
  `creditchargepercentageforinv` decimal(20,2) NOT NULL default '0.00',
  `issystem` enum('Y','N') NOT NULL default 'N',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`creditcardtypekey`),
  UNIQUE KEY `icctcode` (`creditcardtypecode`),
  KEY `icctinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditor` (
  `creditorkey` int(11) unsigned NOT NULL auto_increment,
  `creditorcode` varchar(20) NOT NULL default '',
  `name` varchar(200) NOT NULL default '',
  `name2` varchar(200) NOT NULL default '',
  `regno` varchar(30) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `contactperson` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `phone2` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `fax2` varchar(30) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `email2` varchar(200) NOT NULL default '',
  `homepage` varchar(200) NOT NULL default '',
  `businessform` varchar(200) NOT NULL default '',
  `locationmap` mediumblob,
  `creditlimit` decimal(20,2) unsigned NOT NULL default '0.00',
  `allowexceedcreditlimit` enum('Y','N') NOT NULL default 'Y',
  `addcreditlimit` decimal(20,2) unsigned NOT NULL default '0.00',
  `addcreditlimitvaliddate` date default NULL,
  `suspended` enum('Y','N') NOT NULL default 'N',
  `statementtype` enum('Open Item','Brought Forward') NOT NULL default 'Open Item',
  `note` mediumtext,
  `controlacccode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `categorycode` varchar(20) NOT NULL default '',
  `groupcode` varchar(20) NOT NULL default '',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `acckey` int(11) unsigned NOT NULL default '0',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `gstexemptionno` varchar(30) NOT NULL default '',
  `gstexemptionexpireddate` date default NULL,
  `inhouse` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `gstregno` varchar(30) NOT NULL default '',
  `lastgstverifieddate` date default NULL,
  `city` varchar(50) NOT NULL default '',
  `postalcode` char(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  `gsttypecode` varchar(20) NOT NULL default '',
  `gstregisterdate` date default NULL,
  `selfbillinvoiceapprovalno` varchar(50) NOT NULL default '',
  `selfbillinvoiceapprovaldate` date default NULL,
  `bankcode` varchar(20) NOT NULL default '',
  `accountno` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`creditorkey`),
  UNIQUE KEY `icrcode` (`creditorcode`),
  KEY `icrcontrolacccode` (`controlacccode`),
  KEY `icrareacode` (`areacode`),
  KEY `icrcategorycode` (`categorycode`),
  KEY `icrtermcode` (`termcode`),
  KEY `icrstaffcode` (`staffcode`),
  KEY `icrinactive` (`inactive`),
  KEY `icrcurcode` (`curcode`),
  KEY `icrinhouse` (`inhouse`),
  KEY `icrgroupcode` (`groupcode`),
  KEY `icgstregno` (`gstregno`),
  KEY `iccity` (`city`),
  KEY `icstate` (`state`),
  KEY `iccountrycode` (`countrycode`),
  KEY `icgsttypecode` (`gsttypecode`),
  KEY `icbankcode` (`bankcode`),
  KEY `icaccountno` (`accountno`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorbranchaddress` (
  `creditorbranchaddresskey` int(11) unsigned NOT NULL auto_increment,
  `creditorcode` varchar(20) NOT NULL default '',
  `branchcode` varchar(20) NOT NULL default '',
  `branchname` varchar(200) NOT NULL default '',
  `contactperson` varchar(200) NOT NULL default '',
  `contactpersonposition` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `postalcode` varchar(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  `isdefault` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`creditorbranchaddresskey`),
  KEY `icbacreditorcode` (`creditorcode`),
  KEY `icbabranchcode` (`branchcode`),
  KEY `icbaisdefault` (`isdefault`),
  KEY `icbacity` (`city`),
  KEY `icbastate` (`state`),
  KEY `icbacountrycode` (`countrycode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorcn` (
  `creditorcnkey` int(11) unsigned NOT NULL auto_increment,
  `creditorcncode` varchar(20) NOT NULL default '',
  `creditorcndate` date default NULL,
  `creditorcode` varchar(20) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `multi` enum('Y','N') NOT NULL default 'N',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `fromdoctype` varchar(5) NOT NULL default 'CCN',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `totaltaxableamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `totalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `totallocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  PRIMARY KEY  (`creditorcnkey`),
  UNIQUE KEY `iccncode` (`creditorcncode`),
  KEY `iccndate` (`creditorcndate`),
  KEY `iccncreditorcode` (`creditorcode`),
  KEY `iccnacccode` (`acccode`),
  KEY `iccnrefno` (`refno`),
  KEY `iccnmulti` (`multi`),
  KEY `iccnstaffcode` (`staffcode`),
  KEY `iccndepartmentcode` (`departmentcode`),
  KEY `iccnfromdoctype` (`fromdoctype`),
  KEY `iccnareacode` (`areacode`),
  KEY `iccncurcode` (`curcode`),
  KEY `iccnjobcode` (`jobcode`),
  KEY `iccncancelled` (`cancelled`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorcnmulti` (
  `creditorcnkey` int(11) unsigned NOT NULL default '0',
  `creditorcncode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `subamount` decimal(20,2) NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `localsubamount` decimal(20,2) NOT NULL default '0.00',
  `amountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `localamountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxpercentage` decimal(20,2) default NULL,
  `taxacccode` varchar(20) NOT NULL default '',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxrefno` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`creditorcnkey`,`pos`),
  KEY `iccnmcode` (`creditorcncode`),
  KEY `iccnmacccode` (`acccode`),
  KEY `iccnmdepartmentcode` (`departmentcode`),
  KEY `iccnmjobcode` (`jobcode`),
  KEY `icdnmgsttypecode` (`gsttypecode`),
  KEY `icdnmtaxacccode` (`taxacccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorcnob` (
  `creditorcnkey` int(11) unsigned NOT NULL auto_increment,
  `creditorcncode` varchar(20) NOT NULL default '',
  `creditorcndate` date default NULL,
  `creditorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`creditorcnkey`),
  UNIQUE KEY `iccnobcode` (`creditorcncode`),
  KEY `iccnobdate` (`creditorcndate`),
  KEY `iccnobcreditorcode` (`creditorcode`),
  KEY `iccnobrefno` (`refno`),
  KEY `iccnobstaffcode` (`staffcode`),
  KEY `iccnobdepartmentcode` (`departmentcode`),
  KEY `iccnobareacode` (`areacode`),
  KEY `iccnobcurcode` (`curcode`),
  KEY `iccnobjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorcontact` (
  `creditorcontactkey` int(11) unsigned NOT NULL auto_increment,
  `creditorkey` int(11) unsigned NOT NULL,
  `personincharge` varchar(200) NOT NULL default '',
  `division` varchar(200) NOT NULL default '',
  `title` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `phone2` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `fax2` varchar(30) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `remark1` varchar(200) NOT NULL default '',
  `remark2` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`creditorcontactkey`),
  KEY `iccpersonincharge` (`personincharge`),
  KEY `iccdivision` (`division`),
  KEY `icccreditorkey` (`creditorkey`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditordn` (
  `creditordnkey` int(11) unsigned NOT NULL auto_increment,
  `creditordncode` varchar(20) NOT NULL default '',
  `creditordndate` date default NULL,
  `creditorcode` varchar(20) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `multi` enum('Y','N') NOT NULL default 'N',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `duedate` date default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `fromdoctype` varchar(5) NOT NULL default 'CDN',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `totaltaxableamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `totalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `totallocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  PRIMARY KEY  (`creditordnkey`),
  UNIQUE KEY `icdncode` (`creditordncode`),
  KEY `icdndate` (`creditordndate`),
  KEY `icdncreditorcode` (`creditorcode`),
  KEY `icdnacccode` (`acccode`),
  KEY `icdnrefno` (`refno`),
  KEY `icdnmulti` (`multi`),
  KEY `icdntermcode` (`termcode`),
  KEY `icdnstaffcode` (`staffcode`),
  KEY `icdndepartmentcode` (`departmentcode`),
  KEY `icdnfromdoctype` (`fromdoctype`),
  KEY `icdnareacode` (`areacode`),
  KEY `icdncurcode` (`curcode`),
  KEY `icdnjobcode` (`jobcode`),
  KEY `icdncancelled` (`cancelled`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditordnmulti` (
  `creditordnkey` int(11) unsigned NOT NULL default '0',
  `creditordncode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `subamount` decimal(20,2) NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `localsubamount` decimal(20,2) NOT NULL default '0.00',
  `amountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `localamountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxpercentage` decimal(20,2) default NULL,
  `taxacccode` varchar(20) NOT NULL default '',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxrefno` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`creditordnkey`,`pos`),
  KEY `icdnmcode` (`creditordncode`),
  KEY `icdnmacccode` (`acccode`),
  KEY `icdnmdepartmentcode` (`departmentcode`),
  KEY `icdnmjobcode` (`jobcode`),
  KEY `icdnmgsttypecode` (`gsttypecode`),
  KEY `icdnmtaxacccode` (`taxacccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditordnob` (
  `creditordnkey` int(11) unsigned NOT NULL auto_increment,
  `creditordncode` varchar(20) NOT NULL default '',
  `creditordndate` date default NULL,
  `creditorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `duedate` date default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`creditordnkey`),
  UNIQUE KEY `icdnobcode` (`creditordncode`),
  KEY `icdnobdate` (`creditordndate`),
  KEY `icdnobcreditorcode` (`creditorcode`),
  KEY `icdnobrefno` (`refno`),
  KEY `icdnobtermcode` (`termcode`),
  KEY `icdnobstaffcode` (`staffcode`),
  KEY `icdnobdepartmentcode` (`departmentcode`),
  KEY `icdnobareacode` (`areacode`),
  KEY `icdnobcurcode` (`curcode`),
  KEY `icdnobjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorgstbaddebtrelief` (
  `creditorgstbaddebtreliefkey` int(11) unsigned NOT NULL auto_increment,
  `creditorgstbaddebtreliefcode` varchar(20) NOT NULL default '',
  `creditorgstbaddebtreliefdate` date default NULL,
  `creditorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `gsttypecode` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `paymentamount` decimal(20,2) NOT NULL default '0.00',
  `outstandingamount` decimal(20,2) NOT NULL default '0.00',
  `gstclaimedamount` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `taxableclaimedamount` decimal(20,2) NOT NULL default '0.00',
  `debitacccode` varchar(20) NOT NULL default '',
  `creditacccode` varchar(20) NOT NULL default '',
  `journalkey` int(11) unsigned default NULL,
  PRIMARY KEY  (`creditorgstbaddebtreliefkey`),
  UNIQUE KEY `icgbdrcode` (`creditorgstbaddebtreliefcode`),
  KEY `icgbdrdate` (`creditorgstbaddebtreliefdate`),
  KEY `icgbdrcreditorcode` (`creditorcode`),
  KEY `icgbdrgsttypecode` (`gsttypecode`),
  KEY `icgbdrcancelled` (`cancelled`),
  KEY `idbdrdebitacccode` (`debitacccode`),
  KEY `idbdrcreditacccode` (`creditacccode`),
  KEY `idbdrjournalkey` (`journalkey`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorgstbaddebtreliefitem` (
  `creditorgstbaddebtreliefkey` int(11) unsigned NOT NULL default '0',
  `creditorgstbaddebtreliefcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `docid` int(11) unsigned NOT NULL default '0',
  `docdate` date default NULL,
  `docrefno` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `taxpercentage` decimal(20,2) default NULL,
  `amount` decimal(20,2) NOT NULL default '0.00',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `paymentamount` decimal(20,2) NOT NULL default '0.00',
  `paymentlocalamount` decimal(20,2) NOT NULL default '0.00',
  `outstandingamount` decimal(20,2) NOT NULL default '0.00',
  `outstandinglocalamount` decimal(20,2) NOT NULL default '0.00',
  `gstclaimedamount` decimal(20,2) NOT NULL default '0.00',
  `gstclaimedlocalamount` decimal(20,2) NOT NULL default '0.00',
  `agemonth` int(11) unsigned NOT NULL default '0',
  `taxableclaimedamount` decimal(20,2) NOT NULL default '0.00',
  `taxableclaimedlocalamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`creditorgstbaddebtreliefkey`,`pos`),
  UNIQUE KEY `icgbdricodepos` (`creditorgstbaddebtreliefcode`,`pos`),
  KEY `icgbdridoctypekeypos` (`doctype`,`dockey`,`docpos`),
  KEY `icgbdridoctypecodepos` (`doctype`,`doccode`,`docpos`),
  KEY `icgbdridoctypekeyid` (`doctype`,`dockey`,`docid`),
  KEY `icgbdridoctypecodeid` (`doctype`,`doccode`,`docid`),
  KEY `icgbdricurcode` (`curcode`),
  KEY `icgbdridepartmentcode` (`departmentcode`),
  KEY `icgbdrijobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorgstbaddebtrecover` (
  `creditorgstbaddebtrecoverkey` int(11) unsigned NOT NULL auto_increment,
  `creditorgstbaddebtrecovercode` varchar(20) NOT NULL default '',
  `cashbooktypecode` varchar(20) NOT NULL default '',
  `creditorgstbaddebtrecoverdate` date default NULL,
  `paymentacccode` varchar(20) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `payto` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `chequeno` varchar(20) NOT NULL default '',
  `multi` enum('Y','N') NOT NULL default 'N',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `remark1` varchar(200) NOT NULL,
  `remark2` varchar(200) NOT NULL,
  `remark3` varchar(200) NOT NULL,
  `remark4` varchar(200) NOT NULL,
  `remark5` varchar(200) NOT NULL,
  `remark6` varchar(200) NOT NULL,
  `remark7` varchar(200) NOT NULL,
  `remark8` varchar(200) NOT NULL,
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `bankchargeacccode` varchar(20) NOT NULL default '',
  `bcurcode` varchar(20) NOT NULL default '',
  `bcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `bamount` decimal(20,2) NOT NULL default '0.00',
  `bankcharges` decimal(20,2) NOT NULL default '0.00',
  `blocalcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `blocalamount` decimal(20,2) NOT NULL default '0.00',
  `localbankcharges` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `actualpaymentcode` varchar(20) NOT NULL default '',
  `creditorgstbaddebtreliefcode` varchar(20) NOT NULL default '',
  `doctype` varchar(5) NOT NULL default '',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `gsttypecode` varchar(20) NOT NULL default '',
  `gstrecoveramount` decimal(20,2) NOT NULL default '0.00',
  `gstrecoverlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxablerecoveramount` decimal(20,2) NOT NULL default '0.00',
  `taxablerecoverlocalamount` decimal(20,2) NOT NULL default '0.00',
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default '0',
  `fromdoccode` varchar(20) NOT NULL default '',
  `debitacccode` varchar(20) NOT NULL default '',
  `creditacccode` varchar(20) NOT NULL default '',
  `baddebtacccode` varchar(20) NOT NULL default '',
  `journalkey` int(11) unsigned default NULL,
  `writeoff` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`creditorgstbaddebtrecoverkey`),
  UNIQUE KEY `ipcode` (`creditorgstbaddebtrecovercode`),
  KEY `icgbdrvdate` (`creditorgstbaddebtrecoverdate`),
  KEY `icgbdrvpaymentacccode` (`paymentacccode`),
  KEY `icgbdrvacccode` (`acccode`),
  KEY `icgbdrvchequeno` (`chequeno`),
  KEY `icgbdrvmulti` (`multi`),
  KEY `icgbdrvdepartmentcode` (`departmentcode`),
  KEY `icgbdrvstaffcode` (`staffcode`),
  KEY `icgbdrvareacode` (`areacode`),
  KEY `icgbdrvcancelled` (`cancelled`),
  KEY `icgbdrvcurcode` (`curcode`),
  KEY `icgbdrvcashbooktypecode` (`cashbooktypecode`),
  KEY `icgbdrvjobcode` (`jobcode`),
  KEY `icgbdrvbankchargeacccode` (`bankchargeacccode`),
  KEY `icgbdrvbcurcode` (`bcurcode`),
  KEY `icgbdrvactualpaymentcode` (`actualpaymentcode`),
  KEY `icgbdrvcreditorgstbaddebtreliefcode` (`creditorgstbaddebtreliefcode`),
  KEY `icgbdrvdoctypecodepos` (`doctype`,`doccode`,`docpos`),
  KEY `icgbdrvgsttypecode` (`gsttypecode`),
  KEY `icbdrfromdoctype` (`fromdoctype`),
  KEY `icbdrfromdockey` (`fromdockey`),
  KEY `icbdrfromdoccode` (`fromdoccode`),
  KEY `icbdrdebitacccode` (`debitacccode`),
  KEY `icbdrcreditacccode` (`creditacccode`),
  KEY `icbdrjournalkey` (`journalkey`),
  KEY `icbdrbaddebtacccode` (`baddebtacccode`),
  KEY `icbdrwriteoff` (`writeoff`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE  `creditoritem` (
  `stockkey` int(11) unsigned NOT NULL,
  `stockcode` varchar(30) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `creditorcode` varchar(20) NOT NULL default '',
  `creditoritemcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`stockkey`,`creditorcode`),
  UNIQUE KEY `icistockcreditorcode` (`stockcode`,`creditorcode`),
  KEY `icicreditoritemcode` (`creditoritemcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditoritemlist` (
  `creditoritemlistkey` int(11) unsigned NOT NULL auto_increment,
  `creditorcode` varchar(20) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `creditoritemcode` varchar(30) NOT NULL default '',
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `moq` decimal(20,6) default NULL,
  `std` enum('Y','N') NOT NULL default 'N',
  `leadtime` int(11) unsigned NOT NULL default '0',
  `priority` int(11) unsigned NOT NULL default '0',
  `promotion` enum('Y','N') NOT NULL default 'N',
  `promotiondatefrom` date default NULL,
  `promotiondateto` date default NULL,
  `salestarget` decimal(20,2) default NULL,
  `remark1` varchar(250) NOT NULL default '',
  `remark2` varchar(250) NOT NULL default '',
  `remark3` varchar(250) NOT NULL default '',
  `remark4` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`creditoritemlistkey`),
  KEY `icilcreditorcode` (`creditorcode`),
  KEY `icilstockcode` (`stockcode`),
  KEY `icilcreditoritemcode` (`creditoritemcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditormatched` (
  `paymenttype` varchar(5) NOT NULL default '',
  `paymentcode` varchar(20) NOT NULL default '',
  `paymentpos` int(11) unsigned NOT NULL default '0',
  `paymentdate` date default NULL,
  `fortype` varchar(5) NOT NULL default '',
  `forcode` varchar(20) NOT NULL default '',
  `forpos` int(11) unsigned NOT NULL default '0',
  `fordate` date default NULL,
  `amount` decimal(20,2) unsigned NOT NULL default '0.00',
  `paymentcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `forcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `paymentlocalamount` decimal(20,2) NOT NULL default '0.00',
  `forlocalamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `matchedbeforebaddebt` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`paymenttype`,`paymentcode`,`paymentpos`,`fortype`,`forcode`,`forpos`),
  KEY `icmfortypecodepos` (`fortype`,`forcode`,`forpos`),
  KEY `icmpaydate` (`paymentdate`),
  KEY `icmfordate` (`fordate`),
  KEY `icmmatchedbeforebaddebt` (`matchedbeforebaddebt`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorpricebook` (
  `creditorpricebookkey` int(11) unsigned NOT NULL auto_increment,
  `allcreditor` enum('Y','N') NOT NULL default 'N',
  `creditorcode` varchar(20) NOT NULL default '',
  `categorycode` varchar(20) NOT NULL default '',
  `groupcode` varchar(20) NOT NULL default '',
  `pricecategorycode` varchar(20) NOT NULL default '',
  `pricecategorypos` smallint(5) unsigned NOT NULL default '0',
  `stockcode` varchar(30) NOT NULL default '',
  `stockcategorycode` varchar(20) NOT NULL default '',
  `allstocklocation` enum('Y','N') NOT NULL default 'Y',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `uom` varchar(20) NOT NULL default '',
  `alluom` enum('Y','N') NOT NULL default 'N',
  `pricetype` enum('PRICE','DISCOUNT','MARKUP','PRICEDISCOUNT') NOT NULL default 'PRICE',
  `unitprice` decimal(20,6) NOT NULL default '0.000000',
  `discountformat` varchar(100) default NULL,
  `markup` decimal(20,2) default NULL,
  `minimumqty` decimal(20,6) default NULL,
  `inactive` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`creditorpricebookkey`),
  KEY `icpbkstockcodeuom` (`stockcode`,`uom`),
  KEY `icpbkallcreditor` (`allcreditor`),
  KEY `icpbkcreditorcode` (`creditorcode`),
  KEY `icpbkcategorycode` (`categorycode`),
  KEY `icpbkstockcategorycode` (`stockcategorycode`),
  KEY `icpbkallstocklocation` (`allstocklocation`),
  KEY `icpbkstocklocationcode` (`stocklocationcode`),
  KEY `icpbkalluom` (`alluom`),
  KEY `icpbkpricecategorycode` (`pricecategorycode`),
  KEY `icpbkgroupcode` (`groupcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorpricehistory` (
  `creditorpricehistorykey` int(11) unsigned NOT NULL auto_increment,
  `doccode` varchar(20) NOT NULL default '',
  `docdate` date default NULL,
  `creditorcode` varchar(20) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `qty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `discountamount` decimal(20,2) default NULL,
  `amountbeforediscount` decimal(20,2) default NULL,
  `amount` decimal(20,2) default NULL,
  `competitorprice` decimal(20,6) default NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`creditorpricehistorykey`),
  KEY `icphdoccode` (`doccode`),
  KEY `icphdocdate` (`docdate`),
  KEY `icphdebtorcode` (`creditorcode`),
  KEY `idphstockcode` (`stockcode`),
  KEY `icphcompetitorprice` (`competitorprice`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `creditorproposalmatched` (
  `paymenttype` varchar(5) NOT NULL default '',
  `paymentcode` varchar(20) NOT NULL default '',
  `paymentpos` int(11) unsigned NOT NULL default '0',
  `paymentdate` date default NULL,
  `fortype` varchar(5) NOT NULL default '',
  `forcode` varchar(20) NOT NULL default '',
  `forpos` int(11) unsigned NOT NULL default '0',
  `fordate` date default NULL,
  `amount` decimal(20,2) unsigned NOT NULL default '0.00',
  `paymentcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `forcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `paymentlocalamount` decimal(20,2) NOT NULL default '0.00',
  `forlocalamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`paymenttype`,`paymentcode`,`paymentpos`,`fortype`,`forcode`,`forpos`),
  KEY `icpmfortypecodepos` (`fortype`,`forcode`,`forpos`),
  KEY `icpmpaydate` (`paymentdate`),
  KEY `icpmfordate` (`fordate`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `currency` (
  `curkey` int(11) unsigned NOT NULL auto_increment,
  `curcode` varchar(20) NOT NULL default '',
  `cursymbol` varchar(5) NOT NULL default '',
  `curshortname` varchar(10) NOT NULL default '',
  `curname` varchar(100) NOT NULL default '',
  `buyingrate` decimal(20,8) unsigned NOT NULL default '0.00000000',
  `sellingrate` decimal(20,8) unsigned NOT NULL default '0.00000000',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`curkey`),
  UNIQUE KEY `icurcode` (`curcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `currencyrate` (
  `curcode` varchar(20) NOT NULL default '',
  `datefrom` date NOT NULL default '0000-00-00',
  `dateto` date NOT NULL default '0000-00-00',
  `buyingrate` decimal(20,8) unsigned NOT NULL default '0.00000000',
  `sellingrate` decimal(20,8) unsigned NOT NULL default '0.00000000',
  PRIMARY KEY  (`curcode`,`datefrom`,`dateto`),
  KEY `icurrdatefrom` (`datefrom`),
  KEY `icurrdateto` (`dateto`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customerconsignmentissue` (
  `customerconsignmentissuekey` int(11) unsigned NOT NULL auto_increment,
  `customerconsignmentissuecode` varchar(20) NOT NULL default '',
  `customerconsignmentissuedate` date default NULL,
  `consignmenttypecode` varchar(20) NOT NULL default '',
  `reason` varchar(200) NOT NULL default '',
  `durationindays` int(11) unsigned NOT NULL default '0',
  `debtorcode` varchar(20) NOT NULL default '',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `validity` varchar(100) NOT NULL default '',
  `deliveryterm` varchar(100) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `postalcode` varchar(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  `attention` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `discountamount` decimal(20,2) NOT NULL,
  `amountbeforediscount` decimal(20,2) NOT NULL,
  `amount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertax` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `remark5` varchar(100) NOT NULL,
  `remark6` varchar(100) NOT NULL,
  `remark7` varchar(100) NOT NULL,
  `remark8` varchar(100) NOT NULL,
  `fromdepartmentcode` varchar(20) NOT NULL default '',
  `fromjobcode` varchar(20) NOT NULL default '',
  `fromstocklocationcode` varchar(20) NOT NULL default '',
  `todepartmentcode` varchar(20) NOT NULL default '',
  `tojobcode` varchar(20) NOT NULL default '',
  `tostocklocationcode` varchar(20) NOT NULL default '',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `poststockvalue` enum('Y','N') NOT NULL default 'Y',
  `itemformatkey` int(10) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `branchcode` varchar(20) NOT NULL default '',
  `branchname` varchar(200) NOT NULL default '',
  `docontactperson` varchar(200) NOT NULL default '',
  `dophone` varchar(30) NOT NULL default '',
  `dofax` varchar(30) NOT NULL default '',
  `doemail` varchar(200) NOT NULL default '',
  `doaddress1` varchar(200) NOT NULL default '',
  `doaddress2` varchar(200) NOT NULL default '',
  `doaddress3` varchar(200) NOT NULL default '',
  `doaddress4` varchar(200) NOT NULL default '',
  `docity` varchar(50) NOT NULL default '',
  `dopostalcode` varchar(10) NOT NULL default '',
  `dostate` varchar(50) NOT NULL default '',
  `docountrycode` varchar(50) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localdiscountamount` decimal(20,2) NOT NULL,
  `localamountbeforediscount` decimal(20,2) NOT NULL,
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxamount` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertax` decimal(20,2) NOT NULL default '0.00',
  `ref1` varchar(30) NOT NULL,
  `ref2` varchar(30) NOT NULL,
  `ref3` varchar(30) NOT NULL,
  `ref4` varchar(30) NOT NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `stocktransferkey` int(10) unsigned NOT NULL default '0',
  `debtorname` varchar(200) NOT NULL default '',
  `fromoldversion` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`customerconsignmentissuekey`),
  UNIQUE KEY `ismcode` (`customerconsignmentissuecode`),
  KEY `iccidate` (`customerconsignmentissuedate`),
  KEY `icciconsignmenttypecode` (`consignmenttypecode`),
  KEY `iccidebtorcode` (`debtorcode`),
  KEY `iccitermcode` (`termcode`),
  KEY `iccistaffcode` (`staffcode`),
  KEY `iccirefno` (`refno`),
  KEY `iccifromdepartmentcode` (`fromdepartmentcode`),
  KEY `iccifromstocklocationcode` (`fromstocklocationcode`),
  KEY `iccifromjobcode` (`fromjobcode`),
  KEY `iccitodepartmentcode` (`todepartmentcode`),
  KEY `iccitostocklocationcode` (`tostocklocationcode`),
  KEY `iccitojobcode` (`tojobcode`),
  KEY `iccicancelled` (`cancelled`),
  KEY `iccipoststockvalue` (`poststockvalue`),
  KEY `icciareacode` (`areacode`),
  KEY `iccicurcode` (`curcode`),
  KEY `iccistocktransferkey` (`stocktransferkey`),
  KEY `iccicountrycode` (`countrycode`),
  KEY `iccidocountrycode` (`docountrycode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customerconsignmentissueid` (
  `customerconsignmentissuekey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`customerconsignmentissuekey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customerconsignmentissueitem` (
  `customerconsignmentissuekey` int(11) unsigned NOT NULL default '0',
  `customerconsignmentissuecode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `qty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `unitpricebaseuom` double NOT NULL default '0',
  `discountformat` varchar(100) default NULL,
  `discountamount` decimal(20,2) default NULL,
  `amountbeforediscount` decimal(20,2) default NULL,
  `amount` decimal(20,2) default NULL,
  `gsttypecode` varchar(20) NOT NULL default '',
  `stockpackagegsttypecode` varchar(20) NOT NULL default '',
  `salestaxpercentage` decimal(20,2) default NULL,
  `salestaxamount` decimal(20,2) default NULL,
  `amountaftertax` decimal(20,2) default NULL,
  `debtoritemcode` varchar(30) NOT NULL default '',
  `barcode` varchar(30) NOT NULL default '',
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `rowtype` enum('','TOTALUP','ITEMDISCOUNT','ITEMSERVICETAX','ITEMMISCCHARGES','FOOTERDISCOUNT','FOOTERSERVICETAX','FOOTERMISCCHARGES') NOT NULL,
  `foc` enum('Y','N') NOT NULL default 'N',
  `packingqty` decimal(20,6) default NULL,
  `packinguom` varchar(20) NOT NULL default '',
  `packingrate` decimal(20,6) default NULL,
  `stockpackagecode` varchar(20) NOT NULL,
  `stockpackageid` int(11) unsigned NOT NULL default '0',
  `stockpackagerate` double default NULL,
  `stockpackageqty` decimal(20,6) default NULL,
  `stockpackageunitprice` decimal(20,6) default NULL,
  `stockpackageamount` decimal(20,2) default NULL,
  `stockpackagesalestaxpercentage` decimal(20,2) default NULL,
  `stockpackagesalestaxamount` decimal(20,2) default NULL,
  `stockpackageamountaftertax` decimal(20,2) default NULL,
  `serialno` text,
  `batchno` varchar(30) NOT NULL default '',
  `curcode` varchar(20) NOT NULL default '',
  `curprice` decimal(20,6) default NULL,
  `currate` decimal(20,8) unsigned default NULL,
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default NULL,
  `fromdocid` int(11) unsigned default NULL,
  `fromdoccode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `salestaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagesupplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `stockpackagetaxsubtract` enum('Y','N') NOT NULL default 'N',
  `stockpackagetaxableamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackageamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `stockpackagelocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `stockpackagesalestaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxadjustment` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`customerconsignmentissuekey`,`pos`),
  UNIQUE KEY `icciikeyid` (`customerconsignmentissuekey`,`id`),
  KEY `icciicode` (`customerconsignmentissuecode`),
  KEY `icciistockcode` (`stockcode`),
  KEY `icciibarcode` (`barcode`),
  KEY `icciifoc` (`foc`),
  KEY `icciistockpackagecode` (`stockpackagecode`),
  KEY `icciistockpackageid` (`stockpackageid`),
  KEY `icciibatchno` (`batchno`),
  KEY `icciigsttypecode` (`gsttypecode`),
  KEY `icciistockpackagegsttypecode` (`stockpackagegsttypecode`),
  KEY `icciifromdoctypekeypos` (`fromdoctype`,`fromdockey`,`fromdocid`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customerconsignmentrequest` (
  `customerconsignmentrequestkey` int(11) unsigned NOT NULL auto_increment,
  `customerconsignmentrequestcode` varchar(20) NOT NULL default '',
  `customerconsignmentrequestdate` date default NULL,
  `consignmenttypecode` varchar(20) NOT NULL default '',
  `reason` varchar(200) NOT NULL default '',
  `durationindays` int(11) unsigned NOT NULL default '0',
  `debtorcode` varchar(20) NOT NULL default '',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `validity` varchar(100) NOT NULL default '',
  `deliveryterm` varchar(100) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `postalcode` varchar(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  `attention` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `discountamount` decimal(20,2) NOT NULL,
  `amountbeforediscount` decimal(20,2) NOT NULL,
  `amount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertax` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `remark5` varchar(100) NOT NULL,
  `remark6` varchar(100) NOT NULL,
  `remark7` varchar(100) NOT NULL,
  `remark8` varchar(100) NOT NULL,
  `fromdepartmentcode` varchar(20) NOT NULL default '',
  `todepartmentcode` varchar(20) NOT NULL default '',
  `fromjobcode` varchar(20) NOT NULL default '',
  `tojobcode` varchar(20) NOT NULL default '',
  `fromstocklocationcode` varchar(20) NOT NULL default '',
  `tostocklocationcode` varchar(20) NOT NULL default '',
  `closed` enum('Y','N') NOT NULL default 'N',
  `itemformatkey` int(10) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `branchcode` varchar(20) NOT NULL default '',
  `branchname` varchar(200) NOT NULL default '',
  `docontactperson` varchar(200) NOT NULL default '',
  `dophone` varchar(30) NOT NULL default '',
  `dofax` varchar(30) NOT NULL default '',
  `doemail` varchar(200) NOT NULL default '',
  `doaddress1` varchar(200) NOT NULL default '',
  `doaddress2` varchar(200) NOT NULL default '',
  `doaddress3` varchar(200) NOT NULL default '',
  `doaddress4` varchar(200) NOT NULL default '',
  `docity` varchar(50) NOT NULL default '',
  `dopostalcode` varchar(10) NOT NULL default '',
  `dostate` varchar(50) NOT NULL default '',
  `docountrycode` varchar(50) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localdiscountamount` decimal(20,2) NOT NULL,
  `localamountbeforediscount` decimal(20,2) NOT NULL,
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxamount` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertax` decimal(20,2) NOT NULL default '0.00',
  `approval` enum('Y','N') NOT NULL default 'N',
  `approvalby` varchar(20) NOT NULL,
  `approvaldatetime` datetime default NULL,
  `approvalremark` mediumtext,
  `approval2` enum('Y','N') NOT NULL default 'N',
  `approvalby2` varchar(20) NOT NULL,
  `approvaldatetime2` datetime default NULL,
  `approvalremark2` mediumtext,
  `approval3` enum('Y','N') NOT NULL default 'N',
  `approvalby3` varchar(20) NOT NULL,
  `approvaldatetime3` datetime default NULL,
  `approvalremark3` mediumtext,
  `ref1` varchar(30) NOT NULL,
  `ref2` varchar(30) NOT NULL,
  `ref3` varchar(30) NOT NULL,
  `ref4` varchar(30) NOT NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `debtorname` varchar(200) NOT NULL default '',
  `fromoldversion` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`customerconsignmentrequestkey`),
  UNIQUE KEY `iquocode` (`customerconsignmentrequestcode`),
  KEY `iccfdate` (`customerconsignmentrequestdate`),
  KEY `iccfconsignmenttypecode` (`consignmenttypecode`),
  KEY `iccfdebtorcode` (`debtorcode`),
  KEY `iccftermcode` (`termcode`),
  KEY `iccfstaffcode` (`staffcode`),
  KEY `iccfrefno` (`refno`),
  KEY `isccffromdepartmentcode` (`fromdepartmentcode`),
  KEY `isccftodepartmentcode` (`todepartmentcode`),
  KEY `isccffromstocklocationcode` (`fromstocklocationcode`),
  KEY `isccftostocklocationcode` (`tostocklocationcode`),
  KEY `iccffromjobcode` (`fromjobcode`),
  KEY `iccftojobcode` (`tojobcode`),
  KEY `iccfclosed` (`closed`),
  KEY `iccfareacode` (`areacode`),
  KEY `iccfapproval` (`approval`),
  KEY `iccfapproval2` (`approval2`),
  KEY `iccapproval3` (`approval3`),
  KEY `iccfcurcode` (`curcode`),
  KEY `iccrcountrycode` (`countrycode`),
  KEY `iccrdocountrycode` (`docountrycode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customerconsignmentrequestid` (
  `customerconsignmentrequestkey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`customerconsignmentrequestkey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customerconsignmentrequestitem` (
  `customerconsignmentrequestkey` int(11) unsigned NOT NULL default '0',
  `customerconsignmentrequestcode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `qty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `unitpricebaseuom` double NOT NULL default '0',
  `discountformat` varchar(100) default NULL,
  `discountamount` decimal(20,2) default NULL,
  `amountbeforediscount` decimal(20,2) default NULL,
  `amount` decimal(20,2) default NULL,
  `gsttypecode` varchar(20) NOT NULL default '',
  `stockpackagegsttypecode` varchar(20) NOT NULL default '',
  `salestaxpercentage` decimal(20,2) default NULL,
  `salestaxamount` decimal(20,2) default NULL,
  `amountaftertax` decimal(20,2) default NULL,
  `debtoritemcode` varchar(30) NOT NULL default '',
  `barcode` varchar(30) NOT NULL default '',
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `rowtype` enum('','TOTALUP','ITEMDISCOUNT','ITEMSERVICETAX','ITEMMISCCHARGES','FOOTERDISCOUNT','FOOTERSERVICETAX','FOOTERMISCCHARGES') NOT NULL,
  `foc` enum('Y','N') NOT NULL default 'N',
  `packingqty` decimal(20,6) default NULL,
  `packinguom` varchar(20) NOT NULL default '',
  `packingrate` decimal(20,6) default NULL,
  `stockpackagecode` varchar(20) NOT NULL,
  `stockpackageid` int(11) unsigned NOT NULL default '0',
  `stockpackagerate` double default NULL,
  `stockpackageqty` decimal(20,6) default NULL,
  `stockpackageunitprice` decimal(20,6) default NULL,
  `stockpackageamount` decimal(20,2) default NULL,
  `stockpackagesalestaxpercentage` decimal(20,2) default NULL,
  `stockpackagesalestaxamount` decimal(20,2) default NULL,
  `stockpackageamountaftertax` decimal(20,2) default NULL,
  `curcode` varchar(20) NOT NULL default '',
  `curprice` decimal(20,6) default NULL,
  `currate` decimal(20,8) unsigned default NULL,
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `salestaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagesupplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `stockpackagetaxsubtract` enum('Y','N') NOT NULL default 'N',
  `stockpackagetaxableamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackageamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `stockpackagelocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `stockpackagesalestaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxadjustment` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`customerconsignmentrequestkey`,`pos`),
  UNIQUE KEY `iquoikeyid` (`customerconsignmentrequestkey`,`id`),
  KEY `iccficode` (`customerconsignmentrequestcode`),
  KEY `iccfistockcode` (`stockcode`),
  KEY `iccfibarcode` (`barcode`),
  KEY `iccfifoc` (`foc`),
  KEY `iccfistockpackagecode` (`stockpackagecode`),
  KEY `iccfistockpackageid` (`stockpackageid`),
  KEY `iccfigsttypecode` (`gsttypecode`),
  KEY `iccfistockpackagegsttypecode` (`stockpackagegsttypecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customerconsignmentreturn` (
  `customerconsignmentreturnkey` int(11) unsigned NOT NULL auto_increment,
  `customerconsignmentreturncode` varchar(20) NOT NULL default '',
  `customerconsignmentreturndate` date default NULL,
  `consignmenttypecode` varchar(20) NOT NULL default '',
  `reason` varchar(200) NOT NULL default '',
  `inspection` varchar(200) NOT NULL default '',
  `debtorcode` varchar(20) NOT NULL default '',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `validity` varchar(100) NOT NULL default '',
  `deliveryterm` varchar(100) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `postalcode` varchar(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  `attention` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `discountamount` decimal(20,2) NOT NULL,
  `amountbeforediscount` decimal(20,2) NOT NULL,
  `amount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertax` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `remark5` varchar(100) NOT NULL,
  `remark6` varchar(100) NOT NULL,
  `remark7` varchar(100) NOT NULL,
  `remark8` varchar(100) NOT NULL,
  `fromdepartmentcode` varchar(20) NOT NULL default '',
  `fromjobcode` varchar(20) NOT NULL default '',
  `fromstocklocationcode` varchar(20) NOT NULL default '',
  `todepartmentcode` varchar(20) NOT NULL default '',
  `tojobcode` varchar(20) NOT NULL default '',
  `tostocklocationcode` varchar(20) NOT NULL default '',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `poststockvalue` enum('Y','N') NOT NULL default 'Y',
  `itemformatkey` int(10) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `branchcode` varchar(20) NOT NULL default '',
  `branchname` varchar(200) NOT NULL default '',
  `docontactperson` varchar(200) NOT NULL default '',
  `dophone` varchar(30) NOT NULL default '',
  `dofax` varchar(30) NOT NULL default '',
  `doemail` varchar(200) NOT NULL default '',
  `doaddress1` varchar(200) NOT NULL default '',
  `doaddress2` varchar(200) NOT NULL default '',
  `doaddress3` varchar(200) NOT NULL default '',
  `doaddress4` varchar(200) NOT NULL default '',
  `docity` varchar(50) NOT NULL default '',
  `dopostalcode` varchar(10) NOT NULL default '',
  `dostate` varchar(50) NOT NULL default '',
  `docountrycode` varchar(50) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localdiscountamount` decimal(20,2) NOT NULL,
  `localamountbeforediscount` decimal(20,2) NOT NULL,
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxamount` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertax` decimal(20,2) NOT NULL default '0.00',
  `ref1` varchar(30) NOT NULL,
  `ref2` varchar(30) NOT NULL,
  `ref3` varchar(30) NOT NULL,
  `ref4` varchar(30) NOT NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `stocktransferkey` int(10) unsigned NOT NULL default '0',
  `debtorname` varchar(200) NOT NULL default '',
  `fromoldversion` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`customerconsignmentreturnkey`),
  UNIQUE KEY `ismcode` (`customerconsignmentreturncode`),
  KEY `iccrdate` (`customerconsignmentreturndate`),
  KEY `iccrconsignmenttypecode` (`consignmenttypecode`),
  KEY `iccrdebtorcode` (`debtorcode`),
  KEY `iccrtermcode` (`termcode`),
  KEY `iccrstaffcode` (`staffcode`),
  KEY `iccrrefno` (`refno`),
  KEY `iccrfromdepartmentcode` (`fromdepartmentcode`),
  KEY `iccrfromstocklocationcode` (`fromstocklocationcode`),
  KEY `iccrfromjobcode` (`fromjobcode`),
  KEY `iccrtodepartmentcode` (`todepartmentcode`),
  KEY `iccrtostocklocationcode` (`tostocklocationcode`),
  KEY `iccrtojobcode` (`tojobcode`),
  KEY `iccrcancelled` (`cancelled`),
  KEY `iccrpoststockvalue` (`poststockvalue`),
  KEY `iccrareacode` (`areacode`),
  KEY `iccrcurcode` (`curcode`),
  KEY `iccrstocktransferkey` (`stocktransferkey`),
  KEY `icctcountrycode` (`countrycode`),
  KEY `icctdocountrycode` (`docountrycode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customerconsignmentreturnid` (
  `customerconsignmentreturnkey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`customerconsignmentreturnkey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customerconsignmentreturnitem` (
  `customerconsignmentreturnkey` int(11) unsigned NOT NULL default '0',
  `customerconsignmentreturncode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `qty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `unitpricebaseuom` double NOT NULL default '0',
  `discountformat` varchar(100) default NULL,
  `discountamount` decimal(20,2) default NULL,
  `amountbeforediscount` decimal(20,2) default NULL,
  `amount` decimal(20,2) default NULL,
  `gsttypecode` varchar(20) NOT NULL default '',
  `stockpackagegsttypecode` varchar(20) NOT NULL default '',
  `salestaxpercentage` decimal(20,2) default NULL,
  `salestaxamount` decimal(20,2) default NULL,
  `amountaftertax` decimal(20,2) default NULL,
  `debtoritemcode` varchar(30) NOT NULL default '',
  `barcode` varchar(30) NOT NULL default '',
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `rowtype` enum('','TOTALUP','ITEMDISCOUNT','ITEMSERVICETAX','ITEMMISCCHARGES','FOOTERDISCOUNT','FOOTERSERVICETAX','FOOTERMISCCHARGES') NOT NULL,
  `foc` enum('Y','N') NOT NULL default 'N',
  `packingqty` decimal(20,6) default NULL,
  `packinguom` varchar(20) NOT NULL default '',
  `packingrate` decimal(20,6) default NULL,
  `stockpackagecode` varchar(20) NOT NULL,
  `stockpackageid` int(11) unsigned NOT NULL default '0',
  `stockpackagerate` double default NULL,
  `stockpackageqty` decimal(20,6) default NULL,
  `stockpackageunitprice` decimal(20,6) default NULL,
  `stockpackageamount` decimal(20,2) default NULL,
  `stockpackagesalestaxpercentage` decimal(20,2) default NULL,
  `stockpackagesalestaxamount` decimal(20,2) default NULL,
  `stockpackageamountaftertax` decimal(20,2) default NULL,
  `serialno` text,
  `batchno` varchar(30) NOT NULL default '',
  `curcode` varchar(20) NOT NULL default '',
  `curprice` decimal(20,6) default NULL,
  `currate` decimal(20,8) unsigned default NULL,
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default NULL,
  `fromdocid` int(11) unsigned default NULL,
  `fromdoccode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `salestaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagesupplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `stockpackagetaxsubtract` enum('Y','N') NOT NULL default 'N',
  `stockpackagetaxableamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackageamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `stockpackagelocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `stockpackagesalestaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxadjustment` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`customerconsignmentreturnkey`,`pos`),
  UNIQUE KEY `iccrikeyid` (`customerconsignmentreturnkey`,`id`),
  KEY `iccricode` (`customerconsignmentreturncode`),
  KEY `iccristockcode` (`stockcode`),
  KEY `iccribarcode` (`barcode`),
  KEY `iccrifoc` (`foc`),
  KEY `iccristockpackagecode` (`stockpackagecode`),
  KEY `iccristockpackageid` (`stockpackageid`),
  KEY `iccribatchno` (`batchno`),
  KEY `iccrigsttypecode` (`gsttypecode`),
  KEY `iccrstockpackagegsttypecode` (`stockpackagegsttypecode`),
  KEY `iccrifromdoctypekeypos` (`fromdoctype`,`fromdockey`,`fromdocid`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customfield` (
  `tablename` varchar(100) NOT NULL default '',
  `fielddefs` mediumblob,
  `formcaption` varchar(30) NOT NULL default '',
  `formdefs` mediumblob,
  PRIMARY KEY  (`tablename`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customgsttype` (
  `customgsttypekey` int(11) unsigned NOT NULL auto_increment,
  `customgsttypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `supplypurchase` enum('Supply','Purchase') NOT NULL default 'Supply',
  `taxpercent` decimal(20,2) NOT NULL default '0.00',
  `claimable` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`customgsttypekey`),
  UNIQUE KEY `icgsttcode` (`customgsttypecode`),
  KEY `icgsttclaimable` (`claimable`),
  KEY `icgstttype` (`supplypurchase`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `customqueryitem` (
  `customqueryitemkey` int(11) unsigned NOT NULL auto_increment,
  `customqueryitemcode` varchar(20) NOT NULL default '',
  `customqueryitemtype` set('NONE','QI','SOI','DOI','SII','SDNI','SCNI','SMI','PRI','POI','GRNI','PII','PDNI','PCNI','PMI','SOBI','SADJI','STFI','STRI','STII','STAI','STDI','RII','RCI','RCRI','RDI','CCRI','CCII','CCTI','JOI','STBI') NOT NULL default 'NONE',
  `forfield` varchar(250) NOT NULL default '',
  `expression` mediumtext,
  `triggerfromfields` varchar(250) NOT NULL default '',
  `recalculate` enum('NONE','QTY','UNITPRICE','AMOUNT','TAXPERCENTAGE','PACKINGQTY','PACKINGRATE','CURPRICE','CURRATE','WEIGHTRATE','VOLUMERATE') NOT NULL default 'NONE',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `fromtable` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`customqueryitemkey`),
  UNIQUE KEY `icqicode` (`customqueryitemcode`),
  KEY `icqitype` (`customqueryitemtype`),
  KEY `icqiinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `dataset` (
  `datasetkey` int(11) unsigned NOT NULL auto_increment,
  `reporttype` varchar(30) NOT NULL default '',
  `datasetcode` varchar(20) NOT NULL default '',
  `system` enum('Y','N') NOT NULL default 'N',
  `descr` varchar(200) NOT NULL default '',
  `datausername` varchar(50) NOT NULL default '',
  `masterdataname` varchar(30) NOT NULL default '',
  `masterfieldlink` varchar(100) NOT NULL default '',
  `detailfieldlink` varchar(100) NOT NULL default '',
  `skipwhennorecord` enum('Y','N') NOT NULL default 'N',
  `paramdefs` mediumblob,
  `beforecommand` mediumtext,
  `selectcommand` mediumtext,
  `aftercommand` mediumtext,
  PRIMARY KEY  (`datasetkey`),
  UNIQUE KEY `idsreporttypedatasetcodesystem` (`reporttype`,`datasetcode`,`system`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `dbinfo` (
  `dbcategory` varchar(30) NOT NULL default '',
  `dbname` varchar(250) NOT NULL default '',
  `dbversion` varchar(50) NOT NULL default '',
  `reportversion` varchar(50) NOT NULL default '',
  `created` datetime default NULL,
  `lastchanged` datetime default NULL,
  `masgafversion` varchar(50) NOT NULL default '',
  `sggafversion` varchar(50) NOT NULL default ''
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtor` (
  `debtorkey` int(11) unsigned NOT NULL auto_increment,
  `debtorcode` varchar(20) NOT NULL default '',
  `name` varchar(200) NOT NULL default '',
  `name2` varchar(200) NOT NULL default '',
  `regno` varchar(30) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `contactperson` varchar(200) NOT NULL default '',
  `contactpersonposition` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `phone2` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `fax2` varchar(30) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `email2` varchar(200) NOT NULL default '',
  `homepage` varchar(200) NOT NULL default '',
  `businessform` varchar(200) NOT NULL default '',
  `locationmap` mediumblob,
  `creditlimit` decimal(20,2) unsigned NOT NULL default '0.00',
  `allowexceedcreditlimit` enum('Y','N') NOT NULL default 'Y',
  `addcreditlimit` decimal(20,2) unsigned NOT NULL default '0.00',
  `addcreditlimitvaliddate` date default NULL,
  `suspended` enum('Y','N') NOT NULL default 'N',
  `statementtype` enum('Open Item','Brought Forward') NOT NULL default 'Open Item',
  `note` mediumtext,
  `controlacccode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `categorycode` varchar(20) NOT NULL default '',
  `groupcode` varchar(20) NOT NULL default '',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `staff2code` varchar(20) NOT NULL default '',
  `pricecategorycode` varchar(20) NOT NULL default '',
  `acckey` int(11) unsigned NOT NULL default '0',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `pos` enum('Y','N') NOT NULL default 'N',
  `addtermdays` int(11) unsigned NOT NULL default '0',
  `discountformat` varchar(100) default NULL,
  `curcode` varchar(20) NOT NULL,
  `defaultdepartmentcode` varchar(20) NOT NULL default '',
  `cashdebtor` enum('Y','N') NOT NULL default 'N',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `gstexemptionno` varchar(30) NOT NULL default '',
  `gstexemptionexpireddate` date default NULL,
  `usestocksalesprice` enum('Y','N') NOT NULL default 'N',
  `stocksalesprice` tinyint(4) NOT NULL default '1',
  `footerdiscountschemecode` varchar(20) NOT NULL default '',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `gstregno` varchar(30) NOT NULL default '',
  `lastgstverifieddate` date default NULL,
  `city` varchar(50) NOT NULL default '',
  `postalcode` char(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  `allowexceedtermcontrol` enum('Y','N') NOT NULL default 'Y',
  `gsttypecode` varchar(20) NOT NULL default '',
  `gstregisterdate` date default NULL,
  PRIMARY KEY  (`debtorkey`),
  UNIQUE KEY `idrcode` (`debtorcode`),
  KEY `idrcontrolacccode` (`controlacccode`),
  KEY `idrareacode` (`areacode`),
  KEY `idrcategorycode` (`categorycode`),
  KEY `idrtermcode` (`termcode`),
  KEY `idrstaffcode` (`staffcode`),
  KEY `idrinactive` (`inactive`),
  KEY `idrpos` (`pos`),
  KEY `idrcurcode` (`curcode`),
  KEY `idrstaff2code` (`staff2code`),
  KEY `idrpricecategorycode` (`pricecategorycode`),
  KEY `idrdefaultdepartmentcode` (`defaultdepartmentcode`),
  KEY `idfooterdiscountschemecode` (`footerdiscountschemecode`),
  KEY `idrgroupcode` (`groupcode`),
  KEY `idgstregno` (`gstregno`),
  KEY `idcity` (`city`),
  KEY `idstate` (`state`),
  KEY `idcountrycode` (`countrycode`),
  KEY `idgsttypecode` (`gsttypecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtorcn` (
  `debtorcnkey` int(11) unsigned NOT NULL auto_increment,
  `debtorcncode` varchar(20) NOT NULL default '',
  `debtorcndate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `multi` enum('Y','N') NOT NULL default 'N',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `fromdoctype` varchar(5) NOT NULL default 'DCN',
  `salesamount` decimal(20,2) NOT NULL default '0.00',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `localsalesamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `totaltaxableamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `totalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `totallocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  PRIMARY KEY  (`debtorcnkey`),
  UNIQUE KEY `inddcncode` (`debtorcncode`),
  KEY `idcndate` (`debtorcndate`),
  KEY `idcndebtorcode` (`debtorcode`),
  KEY `idcnacccode` (`acccode`),
  KEY `idcnrefno` (`refno`),
  KEY `idcnmulti` (`multi`),
  KEY `idcnstaffcode` (`staffcode`),
  KEY `idcndepartmentcode` (`departmentcode`),
  KEY `idcnfromdoctype` (`fromdoctype`),
  KEY `idcnareacode` (`areacode`),
  KEY `idcncurcode` (`curcode`),
  KEY `idcnjobcode` (`jobcode`),
  KEY `idcncancelled` (`cancelled`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtorcnmulti` (
  `debtorcnkey` int(11) unsigned NOT NULL default '0',
  `debtorcncode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `subamount` decimal(20,2) NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `nonsales` enum('Y','N') NOT NULL default 'N',
  `localsubamount` decimal(20,2) NOT NULL default '0.00',
  `amountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `localamountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxpercentage` decimal(20,2) default NULL,
  `taxacccode` varchar(20) NOT NULL default '',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxrefno` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`debtorcnkey`,`pos`),
  KEY `idcnmcode` (`debtorcncode`),
  KEY `idcnmacccode` (`acccode`),
  KEY `idcnmdepartmentcode` (`departmentcode`),
  KEY `idcnnonsales` (`nonsales`),
  KEY `idcnmjobcode` (`jobcode`),
  KEY `idcnmgsttypecode` (`gsttypecode`),
  KEY `idcnmtaxacccode` (`taxacccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtorcnob` (
  `debtorcnkey` int(11) unsigned NOT NULL auto_increment,
  `debtorcncode` varchar(20) NOT NULL default '',
  `debtorcndate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`debtorcnkey`),
  UNIQUE KEY `inddcnobcode` (`debtorcncode`),
  KEY `idcnobdate` (`debtorcndate`),
  KEY `idcnobdebtorcode` (`debtorcode`),
  KEY `idcnobrefno` (`refno`),
  KEY `idcnobstaffcode` (`staffcode`),
  KEY `idcnobdepartmentcode` (`departmentcode`),
  KEY `idcnobareacode` (`areacode`),
  KEY `idcnobcurcode` (`curcode`),
  KEY `idcnobjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtorcontact` (
  `debtorcontactkey` int(11) unsigned NOT NULL auto_increment,
  `debtorkey` int(11) unsigned NOT NULL,
  `personincharge` varchar(200) NOT NULL default '',
  `division` varchar(200) NOT NULL default '',
  `title` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `phone2` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `fax2` varchar(30) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `remark1` varchar(200) NOT NULL default '',
  `remark2` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`debtorcontactkey`),
  KEY `idcpersonincharge` (`personincharge`),
  KEY `idcdivision` (`division`),
  KEY `idcdebtorkey` (`debtorkey`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtordeposit` (
  `debtordepositkey` int(11) unsigned NOT NULL auto_increment,
  `debtordepositcode` varchar(20) NOT NULL default '',
  `debtordepositdate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `receivedfrom` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `chequeno` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `remark1` varchar(200) NOT NULL,
  `remark2` varchar(200) NOT NULL,
  `remark3` varchar(200) NOT NULL,
  `remark4` varchar(200) NOT NULL,
  `remark5` varchar(200) NOT NULL,
  `remark6` varchar(200) NOT NULL,
  `remark7` varchar(200) NOT NULL,
  `remark8` varchar(200) NOT NULL,
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `refunded` enum('Y','N') NOT NULL default 'N',
  `refundeddate` date default NULL,
  `refundedremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `deposittype` enum('','Cash','Credit Card','Cheque') NOT NULL default '',
  `depositdebitacccode` varchar(20) NOT NULL default '',
  `depositcreditacccode` varchar(20) NOT NULL default '',
  `depositjournalkey` int(11) unsigned default NULL,
  PRIMARY KEY  (`debtordepositkey`),
  UNIQUE KEY `iddcode` (`debtordepositcode`),
  KEY `idddate` (`debtordepositdate`),
  KEY `idddebtorcode` (`debtorcode`),
  KEY `iddchequeno` (`chequeno`),
  KEY `idddepartmentcode` (`departmentcode`),
  KEY `iddstaffcode` (`staffcode`),
  KEY `iddareacode` (`areacode`),
  KEY `iddrefunded` (`refunded`),
  KEY `idddepositdebitacccode` (`depositdebitacccode`),
  KEY `idddepositcreditacccode` (`depositcreditacccode`),
  KEY `iddjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtordoaddress` (
  `debtordoaddresskey` int(11) unsigned NOT NULL auto_increment,
  `debtorcode` varchar(20) NOT NULL default '',
  `branchcode` varchar(20) NOT NULL default '',
  `branchname` varchar(200) NOT NULL default '',
  `contactperson` varchar(200) NOT NULL default '',
  `contactpersonposition` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `isdefault` enum('Y','N') NOT NULL default 'N',
  `city` varchar(50) NOT NULL default '',
  `postalcode` char(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`debtordoaddresskey`),
  KEY `iddoadebtorcode` (`debtorcode`),
  KEY `iddoabranchcode` (`branchcode`),
  KEY `iddoaisdefault` (`isdefault`),
  KEY `iddoacity` (`city`),
  KEY `iddoastate` (`state`),
  KEY `iddoacountrycode` (`countrycode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtordn` (
  `debtordnkey` int(11) unsigned NOT NULL auto_increment,
  `debtordncode` varchar(20) NOT NULL default '',
  `debtordndate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `multi` enum('Y','N') NOT NULL default 'N',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `duedate` date default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `fromdoctype` varchar(5) NOT NULL default 'DDN',
  `salesamount` decimal(20,2) NOT NULL default '0.00',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `localsalesamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `totaltaxableamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `totalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `totallocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  PRIMARY KEY  (`debtordnkey`),
  UNIQUE KEY `iddncode` (`debtordncode`),
  KEY `iddndate` (`debtordndate`),
  KEY `iddndebtorcode` (`debtorcode`),
  KEY `iddnacccode` (`acccode`),
  KEY `iddnrefno` (`refno`),
  KEY `iddnmulti` (`multi`),
  KEY `iddntermcode` (`termcode`),
  KEY `iddnstaffcode` (`staffcode`),
  KEY `iddndepartmentcode` (`departmentcode`),
  KEY `iddnfromdoctype` (`fromdoctype`),
  KEY `iddnareacode` (`areacode`),
  KEY `iddncurcode` (`curcode`),
  KEY `iddnjobcode` (`jobcode`),
  KEY `iddncancelled` (`cancelled`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtordnmulti` (
  `debtordnkey` int(11) unsigned NOT NULL default '0',
  `debtordncode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `subamount` decimal(20,2) NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `nonsales` enum('Y','N') NOT NULL default 'N',
  `localsubamount` decimal(20,2) NOT NULL default '0.00',
  `amountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `localamountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxpercentage` decimal(20,2) default NULL,
  `taxacccode` varchar(20) NOT NULL default '',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxrefno` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`debtordnkey`,`pos`),
  KEY `iddnmcode` (`debtordncode`),
  KEY `iddnmacccode` (`acccode`),
  KEY `iddnmdepartmentcode` (`departmentcode`),
  KEY `iddnnonsales` (`nonsales`),
  KEY `iddnmjobcode` (`jobcode`),
  KEY `iddnmgsttypecode` (`gsttypecode`),
  KEY `iddnmtaxacccode` (`taxacccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtordnob` (
  `debtordnkey` int(11) unsigned NOT NULL auto_increment,
  `debtordncode` varchar(20) NOT NULL default '',
  `debtordndate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `duedate` date default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`debtordnkey`),
  UNIQUE KEY `iddnobcode` (`debtordncode`),
  KEY `iddnobdate` (`debtordndate`),
  KEY `iddnobdebtorcode` (`debtorcode`),
  KEY `iddnobrefno` (`refno`),
  KEY `iddnobtermcode` (`termcode`),
  KEY `iddnobstaffcode` (`staffcode`),
  KEY `iddnobdepartmentcode` (`departmentcode`),
  KEY `iddnobareacode` (`areacode`),
  KEY `iddnobcurcode` (`curcode`),
  KEY `iddnobjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtorgstbaddebtrelief` (
  `debtorgstbaddebtreliefkey` int(11) unsigned NOT NULL auto_increment,
  `debtorgstbaddebtreliefcode` varchar(20) NOT NULL default '',
  `debtorgstbaddebtreliefdate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `gsttypecode` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `paymentamount` decimal(20,2) NOT NULL default '0.00',
  `outstandingamount` decimal(20,2) NOT NULL default '0.00',
  `gstclaimedamount` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `taxableclaimedamount` decimal(20,2) NOT NULL default '0.00',
  `debitacccode` varchar(20) NOT NULL default '',
  `creditacccode` varchar(20) NOT NULL default '',
  `journalkey` int(11) unsigned default NULL,
  PRIMARY KEY  (`debtorgstbaddebtreliefkey`),
  UNIQUE KEY `idgbdrcode` (`debtorgstbaddebtreliefcode`),
  KEY `idgbdrdate` (`debtorgstbaddebtreliefdate`),
  KEY `idgbdrdebtorcode` (`debtorcode`),
  KEY `idgbdrgsttypecode` (`gsttypecode`),
  KEY `idgbdrcancelled` (`cancelled`),
  KEY `idbdrdebitacccode` (`debitacccode`),
  KEY `idbdrcreditacccode` (`creditacccode`),
  KEY `idbdrjournalkey` (`journalkey`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtorgstbaddebtreliefitem` (
  `debtorgstbaddebtreliefkey` int(11) unsigned NOT NULL default '0',
  `debtorgstbaddebtreliefcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `docid` int(11) unsigned NOT NULL default '0',
  `docdate` date default NULL,
  `docrefno` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `taxpercentage` decimal(20,2) default NULL,
  `amount` decimal(20,2) NOT NULL default '0.00',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `paymentamount` decimal(20,2) NOT NULL default '0.00',
  `paymentlocalamount` decimal(20,2) NOT NULL default '0.00',
  `outstandingamount` decimal(20,2) NOT NULL default '0.00',
  `outstandinglocalamount` decimal(20,2) NOT NULL default '0.00',
  `gstclaimedamount` decimal(20,2) NOT NULL default '0.00',
  `gstclaimedlocalamount` decimal(20,2) NOT NULL default '0.00',
  `agemonth` int(11) unsigned NOT NULL default '0',
  `taxableclaimedamount` decimal(20,2) NOT NULL default '0.00',
  `taxableclaimedlocalamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  USING BTREE (`debtorgstbaddebtreliefkey`,`pos`,`doccode`),
  UNIQUE KEY `idgbdricodepos` (`debtorgstbaddebtreliefcode`,`pos`),
  KEY `idgbdridoctypekeypos` (`doctype`,`dockey`,`docpos`),
  KEY `idgbdridoctypecodepos` (`doctype`,`doccode`,`docpos`),
  KEY `idgbdridoctypekeyid` (`doctype`,`dockey`,`docid`),
  KEY `idgbdridoctypecodeid` (`doctype`,`doccode`,`docid`),
  KEY `idgbdricurcode` (`curcode`),
  KEY `idgbdridepartmentcode` (`departmentcode`),
  KEY `idgbdrijobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtorgstbaddebtrecover` (
  `debtorgstbaddebtrecoverkey` int(11) unsigned NOT NULL auto_increment,
  `debtorgstbaddebtrecovercode` varchar(20) NOT NULL default '',
  `cashbooktypecode` varchar(20) NOT NULL default '',
  `debtorgstbaddebtrecoverdate` date default NULL,
  `receiptacccode` varchar(20) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `receivedfrom` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `chequeno` varchar(20) NOT NULL default '',
  `multi` enum('Y','N') NOT NULL default 'N',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `remark1` varchar(200) NOT NULL,
  `remark2` varchar(200) NOT NULL,
  `remark3` varchar(200) NOT NULL,
  `remark4` varchar(200) NOT NULL,
  `remark5` varchar(200) NOT NULL,
  `remark6` varchar(200) NOT NULL,
  `remark7` varchar(200) NOT NULL,
  `remark8` varchar(200) NOT NULL,
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `chequereceiveddate` date default NULL,
  `chequeissuedby` varchar(200) NOT NULL default '',
  `datedrawable` date default NULL,
  `bankandlocality` varchar(200) NOT NULL default '',
  `trno` varchar(20) NOT NULL,
  `bankchargeacccode` varchar(20) NOT NULL default '',
  `bcurcode` varchar(20) NOT NULL default '',
  `bcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `bamount` decimal(20,2) NOT NULL default '0.00',
  `bankcharges` decimal(20,2) NOT NULL default '0.00',
  `blocalcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `blocalamount` decimal(20,2) NOT NULL default '0.00',
  `localbankcharges` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `actualreceiptcode` varchar(20) NOT NULL default '',
  `debtorgstbaddebtreliefcode` varchar(20) NOT NULL default '',
  `doctype` varchar(5) NOT NULL default '',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `gsttypecode` varchar(20) NOT NULL default '',
  `gstrecoveramount` decimal(20,2) NOT NULL default '0.00',
  `gstrecoverlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxablerecoveramount` decimal(20,2) NOT NULL default '0.00',
  `taxablerecoverlocalamount` decimal(20,2) NOT NULL default '0.00',
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default '0',
  `fromdoccode` varchar(20) NOT NULL default '',
  `debitacccode` varchar(20) NOT NULL default '',
  `creditacccode` varchar(20) NOT NULL default '',
  `baddebtacccode` varchar(20) NOT NULL default '',
  `journalkey` int(11) unsigned default NULL,
  `writeoff` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`debtorgstbaddebtrecoverkey`),
  UNIQUE KEY `ircode` (`debtorgstbaddebtrecovercode`),
  KEY `idgbdrvdate` (`debtorgstbaddebtrecoverdate`),
  KEY `idgbdrvreceiptacccode` (`receiptacccode`),
  KEY `idgbdrvacccode` (`acccode`),
  KEY `idgbdrvchequeno` (`chequeno`),
  KEY `idgbdrvmulti` (`multi`),
  KEY `idgbdrvdepartmentcode` (`departmentcode`),
  KEY `idgbdrvstaffcode` (`staffcode`),
  KEY `idgbdrvareacode` (`areacode`),
  KEY `idgbdrvcancelled` (`cancelled`),
  KEY `idgbdrvcurcode` (`curcode`),
  KEY `idgbdrvcashbooktypecode` (`cashbooktypecode`),
  KEY `idgbdrvjobcode` (`jobcode`),
  KEY `idgbdrvbankchargeacccode` (`bankchargeacccode`),
  KEY `idgbdrvbcurcode` (`bcurcode`),
  KEY `idgbdrvactualreceiptcode` (`actualreceiptcode`),
  KEY `idgbdrvdebtorgstbaddebtreliefcode` (`debtorgstbaddebtreliefcode`),
  KEY `idgbdrvdoctypecodepos` (`doctype`,`doccode`,`docpos`),
  KEY `idgbdrvgsttypecode` (`gsttypecode`),
  KEY `idbdrfromdoctype` (`fromdoctype`),
  KEY `idbdrfromdockey` (`fromdockey`),
  KEY `idbdrfromdoccode` (`fromdoccode`),
  KEY `idbdrdebitacccode` (`debitacccode`),
  KEY `idbdrcreditacccode` (`creditacccode`),
  KEY `idbdrjournalkey` (`journalkey`),
  KEY `idbdrbaddebtacccode` (`baddebtacccode`),
  KEY `idbdrwriteoff` (`writeoff`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtoritem` (
  `stockkey` int(11) unsigned NOT NULL,
  `stockcode` varchar(30) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `debtorcode` varchar(20) NOT NULL default '',
  `debtoritemcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`stockkey`,`debtorcode`),
  UNIQUE KEY `idistockdebtorcode` (`stockcode`,`debtorcode`),
  KEY `ididebtoritemcode` (`debtoritemcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtoritemlist` (
  `debtoritemlistkey` int(11) unsigned NOT NULL auto_increment,
  `debtorcode` varchar(20) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `debtoritemcode` varchar(30) NOT NULL default '',
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `moq` decimal(20,6) default NULL,
  `std` enum('Y','N') NOT NULL default 'N',
  `leadtime` int(11) unsigned NOT NULL default '0',
  `priority` int(11) unsigned NOT NULL default '0',
  `promotion` enum('Y','N') NOT NULL default 'N',
  `promotiondatefrom` date default NULL,
  `promotiondateto` date default NULL,
  `salestarget` decimal(20,2) default NULL,
  `remark1` varchar(250) NOT NULL default '',
  `remark2` varchar(250) NOT NULL default '',
  `remark3` varchar(250) NOT NULL default '',
  `remark4` varchar(250) NOT NULL default '',
  PRIMARY KEY  (`debtoritemlistkey`),
  KEY `idildebtorcode` (`debtorcode`),
  KEY `idilstockcode` (`stockcode`),
  KEY `idildebtoritemcode` (`debtoritemcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtormatched` (
  `paymenttype` varchar(5) NOT NULL default '',
  `paymentcode` varchar(20) NOT NULL default '',
  `paymentpos` int(11) unsigned NOT NULL default '0',
  `paymentdate` date default NULL,
  `fortype` varchar(5) NOT NULL default '',
  `forcode` varchar(20) NOT NULL default '',
  `forpos` int(11) unsigned NOT NULL default '0',
  `fordate` date default NULL,
  `amount` decimal(20,2) unsigned NOT NULL default '0.00',
  `paymentcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `forcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `paymentlocalamount` decimal(20,2) NOT NULL default '0.00',
  `forlocalamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `matchedbeforebaddebt` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`paymenttype`,`paymentcode`,`paymentpos`,`fortype`,`forcode`,`forpos`),
  KEY `idmfortypecodepos` (`fortype`,`forcode`,`forpos`),
  KEY `idmpaydate` (`paymentdate`),
  KEY `idmfordate` (`fordate`),
  KEY `idmmatchedbeforebaddebt` (`matchedbeforebaddebt`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtormultipayment` (
  `debtormultipaymentkey` int(11) unsigned NOT NULL auto_increment,
  `debtormultipaymentcode` varchar(20) NOT NULL default '',
  `debtormultipaymentdate` date default NULL,
  `acccode` varchar(20) NOT NULL default '',
  `receivedfrom` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `remark1` varchar(200) NOT NULL,
  `remark2` varchar(200) NOT NULL,
  `remark3` varchar(200) NOT NULL,
  `remark4` varchar(200) NOT NULL,
  `remark5` varchar(200) NOT NULL,
  `remark6` varchar(200) NOT NULL,
  `remark7` varchar(200) NOT NULL,
  `remark8` varchar(200) NOT NULL,
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `cashacccode` varchar(20) NOT NULL default '',
  `chequeacccode` varchar(20) NOT NULL default '',
  `creditcardacccode` varchar(20) NOT NULL default '',
  `creditcard2acccode` varchar(20) NOT NULL default '',
  `debitcardacccode` varchar(20) NOT NULL default '',
  `discountacccode` varchar(20) NOT NULL default '',
  `cashreceive` decimal(20,2) NOT NULL default '0.00',
  `discountamount` decimal(20,2) NOT NULL default '0.00',
  `creditcard1amount` decimal(20,2) NOT NULL default '0.00',
  `creditcard2amount` decimal(20,2) NOT NULL default '0.00',
  `creditcardtype1` varchar(20) NOT NULL default '',
  `creditcardno1` varchar(50) NOT NULL default '',
  `creditcardnameoncard1` varchar(200) NOT NULL default '',
  `creditcardexpireddate1` varchar(10) NOT NULL default '',
  `creditcardapprovalcode1` varchar(50) NOT NULL default '',
  `creditcardbankname1` varchar(100) NOT NULL,
  `creditcardpaymenttype1` varchar(20) NOT NULL default '',
  `creditcardtype2` varchar(20) NOT NULL default '',
  `creditcardno2` varchar(50) NOT NULL default '',
  `creditcardnameoncard2` varchar(200) NOT NULL default '',
  `creditcardexpireddate2` varchar(10) NOT NULL default '',
  `creditcardapprovalcode2` varchar(50) NOT NULL default '',
  `creditcardbankname2` varchar(100) NOT NULL,
  `creditcardpaymenttype2` varchar(20) NOT NULL default '',
  `debitcardamount` decimal(20,2) NOT NULL default '0.00',
  `debitcarddescr` varchar(200) NOT NULL default '',
  `debitcardrefno` varchar(20) NOT NULL default '',
  `chequeamount` decimal(20,2) NOT NULL default '0.00',
  `chequedescr` varchar(200) NOT NULL default '',
  `chequeno` varchar(20) NOT NULL default '',
  `paymentjournalkey` int(11) unsigned default NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `baddebtgsttypecode` varchar(20) NOT NULL default '',
  `debtorgstbaddebtrecovercode1st` varchar(20) NOT NULL default '',
  `journaldebtorgstbaddebtrecovercode1st` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`debtormultipaymentkey`),
  UNIQUE KEY `idmpcode` (`debtormultipaymentcode`),
  KEY `idmpdate` (`debtormultipaymentdate`),
  KEY `idmpacccode` (`acccode`),
  KEY `idmpdepartmentcode` (`departmentcode`),
  KEY `idmpstaffcode` (`staffcode`),
  KEY `idmpareacode` (`areacode`),
  KEY `idmpcancelled` (`cancelled`),
  KEY `idmpcurcode` (`curcode`),
  KEY `idmpjobcode` (`jobcode`),
  KEY `idmpbaddebtgsttypecode` (`baddebtgsttypecode`),
  KEY `idmpdebtorgstbaddebtrecovercode1st` (`debtorgstbaddebtrecovercode1st`),
  KEY `idmjournaldebtorgstbaddebtrecovercode1st` (`journaldebtorgstbaddebtrecovercode1st`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `debtorpricehistory` (
  `debtorpricehistorykey` int(11) unsigned NOT NULL auto_increment,
  `doccode` varchar(20) NOT NULL default '',
  `docdate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `qty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `discountamount` decimal(20,2) default NULL,
  `amountbeforediscount` decimal(20,2) default NULL,
  `amount` decimal(20,2) default NULL,
  PRIMARY KEY  (`debtorpricehistorykey`),
  KEY `idphdoccode` (`doccode`),
  KEY `idphdocdate` (`docdate`),
  KEY `idphdebtorcode` (`debtorcode`),
  KEY `idphstockcode` (`stockcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `deliveryorder` (
  `deliveryorderkey` int(11) unsigned NOT NULL auto_increment,
  `deliveryordercode` varchar(20) NOT NULL default '',
  `deliveryorderdate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `validity` varchar(100) NOT NULL default '',
  `deliveryterm` varchar(100) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `postalcode` varchar(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  `attention` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `discountamount` decimal(20,2) NOT NULL,
  `amountbeforediscount` decimal(20,2) NOT NULL,
  `amount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertax` decimal(20,2) NOT NULL default '0.00',
  `totaldepositamount` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `remark5` varchar(100) NOT NULL,
  `remark6` varchar(100) NOT NULL,
  `remark7` varchar(100) NOT NULL,
  `remark8` varchar(100) NOT NULL,
  `globaldepartment` enum('Y','N') NOT NULL default 'Y',
  `departmentcode` varchar(20) NOT NULL default '',
  `globaljob` enum('Y','N') NOT NULL default 'N',
  `jobcode` varchar(20) NOT NULL default '',
  `globalstocklocation` enum('Y','N') NOT NULL default 'Y',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `closed` enum('Y','N') NOT NULL default 'N',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `itemformatkey` int(10) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `branchcode` varchar(20) NOT NULL default '',
  `branchname` varchar(200) NOT NULL default '',
  `docontactperson` varchar(200) NOT NULL default '',
  `dophone` varchar(30) NOT NULL default '',
  `dofax` varchar(30) NOT NULL default '',
  `doemail` varchar(200) NOT NULL default '',
  `doaddress1` varchar(200) NOT NULL default '',
  `doaddress2` varchar(200) NOT NULL default '',
  `doaddress3` varchar(200) NOT NULL default '',
  `doaddress4` varchar(200) NOT NULL default '',
  `docity` varchar(50) NOT NULL default '',
  `dopostalcode` varchar(10) NOT NULL default '',
  `dostate` varchar(50) NOT NULL default '',
  `docountrycode` varchar(50) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localdiscountamount` decimal(20,2) NOT NULL,
  `localamountbeforediscount` decimal(20,2) NOT NULL,
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxamount` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertax` decimal(20,2) NOT NULL default '0.00',
  `localtotaldepositamount` decimal(20,2) NOT NULL default '0.00',
  `ref1` varchar(30) NOT NULL,
  `ref2` varchar(30) NOT NULL,
  `ref3` varchar(30) NOT NULL,
  `ref4` varchar(30) NOT NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `salesleadkey` int(10) unsigned NOT NULL default '0',
  `debtorname` varchar(200) NOT NULL default '',
  `fromoldversion` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`deliveryorderkey`),
  UNIQUE KEY `idocode` (`deliveryordercode`),
  KEY `idodate` (`deliveryorderdate`),
  KEY `idodebtorcode` (`debtorcode`),
  KEY `idotermcode` (`termcode`),
  KEY `idostaffcode` (`staffcode`),
  KEY `idorefno` (`refno`),
  KEY `idodepartmentcode` (`departmentcode`),
  KEY `idostocklocationcode` (`stocklocationcode`),
  KEY `idoclosed` (`closed`),
  KEY `idocancelled` (`cancelled`),
  KEY `idoareacode` (`areacode`),
  KEY `idocurcode` (`curcode`),
  KEY `idojobcode` (`jobcode`),
  KEY `idosalesleadkey` (`salesleadkey`),
  KEY `idocountrycode` (`countrycode`),
  KEY `idodocountrycode` (`docountrycode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `deliveryorderid` (
  `deliveryorderkey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`deliveryorderkey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `deliveryorderitem` (
  `deliveryorderkey` int(11) unsigned NOT NULL default '0',
  `deliveryordercode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `qty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `unitpricebaseuom` double NOT NULL default '0',
  `discountformat` varchar(100) default NULL,
  `discountamount` decimal(20,2) default NULL,
  `amountbeforediscount` decimal(20,2) default NULL,
  `amount` decimal(20,2) default NULL,
  `gsttypecode` varchar(20) NOT NULL default '',
  `stockpackagegsttypecode` varchar(20) NOT NULL default '',
  `salestaxpercentage` decimal(20,2) default NULL,
  `salestaxamount` decimal(20,2) default NULL,
  `amountaftertax` decimal(20,2) default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `delivered` decimal(20,6) default NULL,
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default NULL,
  `fromdocid` int(11) unsigned default NULL,
  `fromdoccode` varchar(20) NOT NULL default '',
  `debtoritemcode` varchar(30) NOT NULL default '',
  `barcode` varchar(30) NOT NULL default '',
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `rowtype` enum('','TOTALUP','ITEMDISCOUNT','ITEMSERVICETAX','ITEMMISCCHARGES','FOOTERDISCOUNT','FOOTERSERVICETAX','FOOTERMISCCHARGES') NOT NULL,
  `foc` enum('Y','N') NOT NULL default 'N',
  `packingqty` decimal(20,6) default NULL,
  `packinguom` varchar(20) NOT NULL default '',
  `packingrate` decimal(20,6) default NULL,
  `stockpackagecode` varchar(20) NOT NULL,
  `stockpackageid` int(11) unsigned NOT NULL default '0',
  `stockpackagerate` double default NULL,
  `stockpackageqty` decimal(20,6) default NULL,
  `stockpackagedelivered` decimal(20,6) default NULL,
  `stockpackageunitprice` decimal(20,6) default NULL,
  `stockpackageamount` decimal(20,2) default NULL,
  `stockpackagesalestaxpercentage` decimal(20,2) default NULL,
  `stockpackagesalestaxamount` decimal(20,2) default NULL,
  `stockpackageamountaftertax` decimal(20,2) default NULL,
  `serialno` text,
  `batchno` varchar(30) NOT NULL default '',
  `curcode` varchar(20) NOT NULL default '',
  `curprice` decimal(20,6) default NULL,
  `currate` decimal(20,8) unsigned default NULL,
  `recommendpricebaseuom` decimal(20,6) unsigned NOT NULL default '0.000000',
  `weightrate` decimal(20,6) unsigned NOT NULL default '0.000000',
  `weightuom` varchar(20) NOT NULL default '',
  `weightqty` decimal(20,6) unsigned NOT NULL default '0.000000',
  `weightloosed` decimal(20,6) unsigned NOT NULL default '0.000000',
  `totalweight` decimal(20,6) unsigned NOT NULL default '0.000000',
  `volumerate` decimal(20,6) unsigned NOT NULL default '0.000000',
  `volumeuom` varchar(20) NOT NULL default '',
  `volumeqty` decimal(20,6) unsigned NOT NULL default '0.000000',
  `volumeloosed` decimal(20,6) unsigned NOT NULL default '0.000000',
  `totalvolume` decimal(20,6) unsigned NOT NULL default '0.000000',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `salestaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `oursocode` varchar(20) NOT NULL default '',
  `oursodate` date default NULL,
  `yourpocode` varchar(20) NOT NULL default '',
  `yourpodate` date default NULL,
  `stockpackagesupplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `stockpackagetaxsubtract` enum('Y','N') NOT NULL default 'N',
  `stockpackagetaxableamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackageamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `stockpackagelocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `stockpackagesalestaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxadjustment` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `stockpackagetaxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`deliveryorderkey`,`pos`),
  UNIQUE KEY `idoikeyid` (`deliveryorderkey`,`id`),
  KEY `idoicode` (`deliveryordercode`),
  KEY `idoistockcode` (`stockcode`),
  KEY `idoidepartmentcode` (`departmentcode`),
  KEY `idoistocklocationcode` (`stocklocationcode`),
  KEY `idoifromdoctypekeypos` (`fromdoctype`,`fromdockey`,`fromdocid`),
  KEY `idoibarcode` (`barcode`),
  KEY `idoifoc` (`foc`),
  KEY `idoistockpackagecode` (`stockpackagecode`),
  KEY `idoistockpackageid` (`stockpackageid`),
  KEY `idoibatchno` (`batchno`),
  KEY `idoijobcode` (`jobcode`),
  KEY `idoigsttypecode` (`gsttypecode`),
  KEY `idoistockpackagegsttypecode` (`stockpackagegsttypecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `department` (
  `departmentkey` int(11) unsigned NOT NULL auto_increment,
  `departmentcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `note` mediumtext,
  `level` tinyint(3) unsigned NOT NULL default '0',
  `rootkey` int(11) unsigned NOT NULL default '0',
  `parentkey` int(11) unsigned NOT NULL default '0',
  `linkkey` varchar(250) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `debtorcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `statuscode` varchar(20) NOT NULL default '',
  `manpoweracccode` varchar(20) NOT NULL default '',
  `startdate` date default NULL,
  `duedate` date default NULL,
  `actualenddate` date default NULL,
  `remark1` varchar(200) NOT NULL default '',
  `remark2` varchar(200) NOT NULL default '',
  `remark3` varchar(200) NOT NULL default '',
  `remark4` varchar(200) NOT NULL default '',
  `remark5` varchar(200) NOT NULL default '',
  `remark6` varchar(200) NOT NULL default '',
  `remark7` varchar(200) NOT NULL default '',
  `remark8` varchar(200) NOT NULL default '',
  `contractamount` decimal(20,2) NOT NULL default '0.00',
  `manpoweramount` decimal(20,2) NOT NULL default '0.00',
  `pettycashamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`departmentkey`),
  UNIQUE KEY `idpcode` (`departmentcode`),
  KEY `idplevel` (`level`),
  KEY `idprootkey` (`rootkey`),
  KEY `idpparentkey` (`parentkey`),
  KEY `idplinkkey` (`linkkey`),
  KEY `idpinactive` (`inactive`),
  KEY `idpdebtorcode` (`debtorcode`),
  KEY `idpstaffcode` (`staffcode`),
  KEY `idpareacode` (`areacode`),
  KEY `idpstatuscode` (`statuscode`),
  KEY `idpmanpoweracccode` (`manpoweracccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `dmrsetup` (
  `dmrsetupkey` int(11) unsigned NOT NULL auto_increment,
  `dmrsetupcode` varchar(50) NOT NULL default '',
  `customgsttypekeylist` mediumtext,
  PRIMARY KEY  (`dmrsetupkey`),
  UNIQUE KEY `idmrsdmrsetupcode` (`dmrsetupcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `docno` (
  `docnokey` int(11) unsigned NOT NULL auto_increment,
  `doctype` varchar(5) NOT NULL default '',
  `descr` varchar(100) NOT NULL default '',
  `string1` varchar(20) NOT NULL default '',
  `usedate` enum('Y','N') NOT NULL default 'Y',
  `dateformatstr` varchar(20) NOT NULL default '',
  `string2` varchar(20) NOT NULL default '',
  `noofdigit` smallint(5) unsigned NOT NULL default '1',
  `nextno` int(11) unsigned NOT NULL default '1',
  `iscustom` enum('Y','N') NOT NULL default 'N',
  `xmlformat` text,
  `issystem` enum('Y','N') NOT NULL default 'N',
  `isdefault` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`docnokey`),
  KEY `idnodoctype` (`doctype`),
  KEY `idnoissystem` (`issystem`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `docnobankcash` (
  `acccode` varchar(20) NOT NULL default '',
  `receiptdocnokey` int(11) unsigned NOT NULL default '0',
  `receiptchequedocnokey` int(11) unsigned NOT NULL default '0',
  `paymentdocnokey` int(11) unsigned NOT NULL default '0',
  `paymentchequedocnokey` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`acccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `draftfolder` (
  `draftfolderkey` int(11) unsigned NOT NULL auto_increment,
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `debtorcreditorcode` varchar(20) NOT NULL,
  `debtorcreditorname` varchar(200) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `draftdata` mediumblob,
  `usercode` varchar(20) NOT NULL default '',
  `modified` datetime default NULL,
  PRIMARY KEY  (`draftfolderkey`),
  KEY `idfdoctype` (`doctype`),
  KEY `idfdockey` (`dockey`),
  KEY `idfdoccode` (`doccode`),
  KEY `idfdebtorcreditorcode` (`debtorcreditorcode`),
  KEY `idfdebtorcreditorname` (`debtorcreditorname`),
  KEY `idfdescr` (`descr`),
  KEY `idfusercode` (`usercode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `emailmanager` (
  `emailkey` int(11) unsigned NOT NULL auto_increment,
  `emailcode` varchar(20) NOT NULL default '',
  `emaildate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `emailfrom` varchar(200) NOT NULL default '',
  `subject` varchar(200) NOT NULL default '',
  `emailcontent` mediumtext,
  `usercode` varchar(20) NOT NULL default '',
  `sendfreq` int(11) unsigned NOT NULL default '0',
  `lastsentdatetime` datetime default NULL,
  PRIMARY KEY  (`emailkey`),
  UNIQUE KEY `iememailcode` (`emailcode`),
  KEY `iemusercode` (`usercode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `emailreceiver` (
  `emailreceiverkey` int(11) unsigned NOT NULL auto_increment,
  `emailcode` varchar(20) NOT NULL default '',
  `emailto` varchar(200) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`emailreceiverkey`),
  KEY `ieremailcode` (`emailcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `emailattachment` (
  `emailattachmentkey` int(11) unsigned NOT NULL auto_increment,
  `emailcode` varchar(20) NOT NULL default '',
  `attachmentfilename` varchar(250) NOT NULL default '',
  `attachment` mediumblob,
  `attachmentsize` int(10) unsigned NOT NULL default '0',
  `attachmentsizeinword` varchar(10) NOT NULL default '',
  PRIMARY KEY  (`emailattachmentkey`),
  KEY `ieaemailcode` (`emailcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `excelreport` (
  `reporttype` varchar(30) NOT NULL default '',
  `reportcode` varchar(20) NOT NULL default '',
  `pagedatafield` varchar(50) NOT NULL default '',
  `pagedatasetname` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `reportblob` mediumblob,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`reporttype`,`reportcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `excelreportseq` (
  `reporttype` varchar(30) NOT NULL default '',
  `reportcode` varchar(20) NOT NULL default '',
  `seq` int(11) NOT NULL default '0',
  `usercode` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`reporttype`,`reportcode`,`usercode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `falink` (
  `falinkkey` int(11) unsigned NOT NULL auto_increment,
  `faacccode` varchar(20) NOT NULL default '',
  `fadacccode` varchar(20) NOT NULL default '',
  `fapldacccode` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`falinkkey`),
  KEY `ifalfaacccode` (`faacccode`),
  KEY `ifalfadacccode` (`fadacccode`),
  KEY `iprifapldacccode` (`fapldacccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `footerdiscountscheme` (
  `footerdiscountschemekey` int(11) unsigned NOT NULL auto_increment,
  `footerdiscountschemecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`footerdiscountschemekey`),
  UNIQUE KEY `icmbaode` (`footerdiscountschemecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `footerdiscountschemeitem` (
  `footerdiscountschemecode` varchar(20) NOT NULL default '',
  `docamount` decimal(20,2) NOT NULL default '0.00',
  `discountpercent` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`footerdiscountschemecode`,`docamount`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `foreignbankadjustment` (
  `foreignbankadjustmentkey` int(11) unsigned NOT NULL auto_increment,
  `foreignbankadjustmentcode` varchar(20) NOT NULL default '',
  `foreignbankadjustmentdate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `descr2` varchar(200) NOT NULL default '',
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `foreignexchangegainacccode` varchar(20) NOT NULL default '',
  `foreignexchangelossacccode` varchar(20) NOT NULL default '',
  `foreignbankadjustmentjournalkey` int(11) unsigned default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`foreignbankadjustmentkey`),
  UNIQUE KEY `ifbacode` (`foreignbankadjustmentcode`),
  KEY `ifabdate` (`foreignbankadjustmentdate`),
  KEY `ifabforeignexchangegainacccode` (`foreignexchangegainacccode`),
  KEY `ifabdepartmentcode` (`departmentcode`),
  KEY `ifabjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `foreignbankadjustmentitem` (
  `foreignbankadjustmentkey` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `category` enum('BS','PL') NOT NULL default 'BS',
  `drcr` enum('DR','CR') NOT NULL default 'DR',
  `curcode` varchar(20) NOT NULL default '',
  `curname` varchar(200) NOT NULL default '',
  `balance` decimal(20,2) NOT NULL default '0.00',
  `localbalance` decimal(20,2) NOT NULL default '0.00',
  `actuallocalbalance` decimal(20,2) NOT NULL default '0.00',
  `adjustmentamount` decimal(20,2) NOT NULL default '0.00',
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `selected` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`foreignbankadjustmentkey`,`pos`),
  KEY `ifbaiacccode` (`acccode`),
  KEY `ifbaicurcode` (`curcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `grn` (
  `grnkey` int(11) unsigned NOT NULL auto_increment,
  `grncode` varchar(20) NOT NULL default '',
  `grndate` date default NULL,
  `creditorcode` varchar(20) NOT NULL default '',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `validity` varchar(100) NOT NULL default '',
  `deliveryterm` varchar(100) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `attention` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `fax` varchar(30) NOT NULL default '',
  `discountamount` decimal(20,2) NOT NULL,
  `amountbeforediscount` decimal(20,2) NOT NULL,
  `amount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertax` decimal(20,2) NOT NULL default '0.00',
  `localtaxamount` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertax` decimal(20,2) NOT NULL default '0.00',
  `note` mediumtext,
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `remark5` varchar(100) NOT NULL,
  `remark6` varchar(100) NOT NULL,
  `remark7` varchar(100) NOT NULL,
  `remark8` varchar(100) NOT NULL,
  `globaldepartment` enum('Y','N') NOT NULL default 'Y',
  `departmentcode` varchar(20) NOT NULL default '',
  `globaljob` enum('Y','N') NOT NULL default 'N',
  `jobcode` varchar(20) NOT NULL default '',
  `globalstocklocation` enum('Y','N') NOT NULL default 'Y',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `closed` enum('Y','N') NOT NULL default 'N',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `itemformatkey` int(11) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `lcost1` decimal(20,2) NOT NULL default '0.00',
  `lcost2` decimal(20,2) NOT NULL default '0.00',
  `lcost3` decimal(20,2) NOT NULL default '0.00',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localdiscountamount` decimal(20,2) NOT NULL,
  `localamountbeforediscount` decimal(20,2) NOT NULL,
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `locallcost1` decimal(20,2) NOT NULL default '0.00',
  `locallcost2` decimal(20,2) NOT NULL default '0.00',
  `locallcost3` decimal(20,2) NOT NULL default '0.00',
  `creditordono` varchar(30) NOT NULL,
  `ref1` varchar(30) NOT NULL,
  `ref2` varchar(30) NOT NULL,
  `ref3` varchar(30) NOT NULL,
  `ref4` varchar(30) NOT NULL,
  `postaccountvalue` enum('Y','N') NOT NULL default 'N',
  `accountjournalkey` int(11) unsigned default '0',
  `purchasesrefno` varchar(20) NOT NULL default '',
  `purchasesdescr` varchar(200) NOT NULL default '',
  `purchasesdepartmentcode` varchar(20) NOT NULL default '',
  `purchasesjobcode` varchar(20) NOT NULL default '',
  `purchasesacccode` varchar(20) NOT NULL default '',
  `accrualrefno` varchar(20) NOT NULL default '',
  `accrualdescr` varchar(200) NOT NULL default '',
  `accrualdepartmentcode` varchar(20) NOT NULL default '',
  `accrualjobcode` varchar(20) NOT NULL default '',
  `accrualacccode` varchar(20) NOT NULL default '',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `creditorname` varchar(200) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `postalcode` varchar(10) NOT NULL default '',
  `state` varchar(50) NOT NULL default '',
  `countrycode` varchar(50) NOT NULL default '',
  `branchcode` varchar(20) NOT NULL default '',
  `branchname` varchar(200) NOT NULL default '',
  `branchcontactperson` varchar(200) NOT NULL default '',
  `branchphone` varchar(30) NOT NULL default '',
  `branchfax` varchar(30) NOT NULL default '',
  `branchemail` varchar(200) NOT NULL default '',
  `branchaddress1` varchar(200) NOT NULL default '',
  `branchaddress2` varchar(200) NOT NULL default '',
  `branchaddress3` varchar(200) NOT NULL default '',
  `branchaddress4` varchar(200) NOT NULL default '',
  `branchcity` varchar(50) NOT NULL default '',
  `branchpostalcode` varchar(10) NOT NULL default '',
  `branchstate` varchar(50) NOT NULL default '',
  `branchcountrycode` varchar(50) NOT NULL default '',
  `fromoldversion` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`grnkey`),
  UNIQUE KEY `igrncode` (`grncode`),
  KEY `igrndate` (`grndate`),
  KEY `igrncreditorcode` (`creditorcode`),
  KEY `igrntermcode` (`termcode`),
  KEY `igrnstaffcode` (`staffcode`),
  KEY `igrnrefno` (`refno`),
  KEY `igrndepartmentcode` (`departmentcode`),
  KEY `igrnstocklocationcode` (`stocklocationcode`),
  KEY `igrnclosed` (`closed`),
  KEY `igrncancelled` (`cancelled`),
  KEY `igrnareacode` (`areacode`),
  KEY `igrncurcode` (`curcode`),
  KEY `igrnpostaccountvalue` (`postaccountvalue`),
  KEY `igrnpurchasesdepartmentcode` (`purchasesdepartmentcode`),
  KEY `igrnpurchasesacccode` (`purchasesacccode`),
  KEY `igrnaccrualdepartmentcode` (`accrualdepartmentcode`),
  KEY `igrnaccrualacccode` (`accrualacccode`),
  KEY `igrnjobcode` (`jobcode`),
  KEY `igrnpurchasesjobcode` (`purchasesjobcode`),
  KEY `igrnaccrualjobcode` (`accrualjobcode`),
  KEY `igrncountrycode` (`countrycode`),
  KEY `igrnbranchcountrycode` (`branchcountrycode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `grnid` (
  `grnkey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`grnkey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `grnitem` (
  `grnkey` int(11) unsigned NOT NULL default '0',
  `grncode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `qty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `unitpricebaseuom` double NOT NULL default '0',
  `discountformat` varchar(100) default NULL,
  `discountamount` decimal(20,2) default NULL,
  `amountbeforediscount` decimal(20,2) default NULL,
  `amount` decimal(20,2) default NULL,
  `gsttypecode` varchar(20) NOT NULL default '',
  `purchasestaxpercentage` decimal(20,2) default NULL,
  `purchasestaxamount` decimal(20,2) default NULL,
  `amountaftertax` decimal(20,2) default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default NULL,
  `fromdocid` int(11) unsigned default NULL,
  `fromdoccode` varchar(20) NOT NULL default '',
  `creditoritemcode` varchar(30) NOT NULL default '',
  `barcode` varchar(30) NOT NULL default '',
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `rowtype` enum('','TOTALUP','ITEMDISCOUNT','ITEMSERVICETAX','ITEMMISCCHARGES','FOOTERDISCOUNT','FOOTERSERVICETAX','FOOTERMISCCHARGES') NOT NULL,
  `foc` enum('Y','N') NOT NULL default 'N',
  `packingqty` decimal(20,6) default NULL,
  `packinguom` varchar(20) NOT NULL default '',
  `packingrate` decimal(20,6) default NULL,
  `serialno` text,
  `lcost1` decimal(20,2) default NULL,
  `lcost2` decimal(20,2) default NULL,
  `lcost3` decimal(20,2) default NULL,
  `batchno` varchar(30) NOT NULL default '',
  `curcode` varchar(20) NOT NULL default '',
  `curprice` decimal(20,6) default NULL,
  `currate` decimal(20,8) unsigned default NULL,
  `recommendpricebaseuom` decimal(20,6) unsigned NOT NULL default '0.000000',
  `weightrate` decimal(20,6) unsigned NOT NULL default '0.000000',
  `weightuom` varchar(20) NOT NULL default '',
  `weightqty` decimal(20,6) unsigned NOT NULL default '0.000000',
  `weightloosed` decimal(20,6) unsigned NOT NULL default '0.000000',
  `totalweight` decimal(20,6) unsigned NOT NULL default '0.000000',
  `volumerate` decimal(20,6) unsigned NOT NULL default '0.000000',
  `volumeuom` varchar(20) NOT NULL default '',
  `volumeqty` decimal(20,6) unsigned NOT NULL default '0.000000',
  `volumeloosed` decimal(20,6) unsigned NOT NULL default '0.000000',
  `totalvolume` decimal(20,6) unsigned NOT NULL default '0.000000',
  `lcosttype1` enum('Value','Quantity') NOT NULL default 'Value',
  `lcosttype2` enum('Value','Quantity') NOT NULL default 'Value',
  `lcosttype3` enum('Value','Quantity') NOT NULL default 'Value',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `purchasestaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `ourpocode` varchar(20) NOT NULL default '',
  `ourpodate` date default NULL,
  PRIMARY KEY  (`grnkey`,`pos`),
  UNIQUE KEY `igrnikeyid` (`grnkey`,`id`),
  KEY `igrnicode` (`grncode`),
  KEY `igrnistockcode` (`stockcode`),
  KEY `igrnidepartmentcode` (`departmentcode`),
  KEY `igrnistocklocationcode` (`stocklocationcode`),
  KEY `igrnifromdoctypekeypos` (`fromdoctype`,`fromdockey`,`fromdocid`),
  KEY `igrnibarcode` (`barcode`),
  KEY `igrnifoc` (`foc`),
  KEY `igrnibatchno` (`batchno`),
  KEY `igrnijobcode` (`jobcode`),
  KEY `igrnigsttypecode` (`gsttypecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `gstcategory` (
  `gstcategorykey` int(11) unsigned NOT NULL auto_increment,
  `gstcategorycode` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`gstcategorykey`),
  UNIQUE KEY `igstccode` (`gstcategorycode`),
  KEY `igstcinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `gstreturnsetup` (
  `gstreturnsetupkey` int(11) unsigned NOT NULL auto_increment,
  `gstreturnsetupcode` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `supplypurchase` enum('Supply','Purchase') NOT NULL default 'Supply',
  `gsttypekeylist` mediumtext,
  `acckeylist` mediumtext,
  `valuefrom` varchar(500) NOT NULL default '',
  `rules` varchar(250) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`gstreturnsetupkey`),
  UNIQUE KEY `igrsgstreturnsetupcode` (`gstreturnsetupcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `gsttrans` (
  `gsttranskey` bigint(20) unsigned NOT NULL auto_increment,
  `gsttransdate` date default NULL,
  `gstdescr` varchar(200) NOT NULL default '',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('Supply','Purchase') NOT NULL default 'Supply',
  `taxpercentage` decimal(20,2) default NULL,
  `taxableacccode` varchar(20) NOT NULL default '',
  `taxableaccname` varchar(200) NOT NULL default '',
  `taxacccode` varchar(20) NOT NULL default '',
  `taxaccname` varchar(200) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxpermitno` varchar(50) NOT NULL default '',
  `taxexportcountrycode` varchar(50) NOT NULL default '',
  `taxrefno` varchar(50) NOT NULL default '',
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `docid` int(11) unsigned NOT NULL default '0',
  `docrefno` varchar(20) NOT NULL default '',
  `docrefno2` varchar(30) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `regno` varchar(30) NOT NULL default '',
  `msiccode` varchar(20) NOT NULL default '',
  `gst03_item16` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`gsttranskey`),
  KEY `igsttdate` (`gsttransdate`),
  KEY `igstttypecode` (`gsttypecode`),
  KEY `igstttaxableacccode` (`taxableacccode`),
  KEY `igstttaxacccode` (`taxacccode`),
  KEY `igsttdoctypekeypos` (`doctype`,`dockey`,`docpos`),
  KEY `igsttdoctypecodepos` (`doctype`,`doccode`,`docpos`),
  KEY `igsttdoctypekeyid` (`doctype`,`dockey`,`docid`),
  KEY `igsttdoctypecodeid` (`doctype`,`doccode`,`docid`),
  KEY `igsttcurcode` (`curcode`),
  KEY `igsttdepartmentcode` (`departmentcode`),
  KEY `igsttjobcode` (`jobcode`),
  KEY `igstttaxexportcountrycode` (`taxexportcountrycode`),
  KEY `igsttmsiccode` (`msiccode`),
  KEY `igtgst03_item16` (`gst03_item16`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `gsttype` (
  `gsttypekey` int(11) unsigned NOT NULL auto_increment,
  `gsttypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `taxpercent` decimal(20,2) NOT NULL default '0.00',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `gstcategorycode` varchar(50) NOT NULL default '',
  `supplypurchase` enum('Supply','Purchase') NOT NULL default 'Supply',
  `gstacccode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `customgsttypecode` varchar(30) NOT NULL default '',
  `issystem` enum('Y','N') NOT NULL default 'N',
  `gst03_item16` enum('Y','N') NOT NULL default 'N',
  `cgainterval` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`gsttypekey`),
  UNIQUE KEY `igsttcode` (`gsttypecode`),
  KEY `igsttinactive` (`inactive`),
  KEY `igsttgstcategorycode` (`gstcategorycode`),
  KEY `igstttype` (`supplypurchase`),
  KEY `igsttgstacccode` (`gstacccode`),
  KEY `igtcustomgsttypecode` (`customgsttypecode`),
  KEY `igtissystem` (`issystem`),
  KEY `igtgst03_item16` (`gst03_item16`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `inquiryfilter` (
  `inquiryfilterkey` int(11) unsigned NOT NULL auto_increment,
  `inquirycode` varchar(20) NOT NULL default '',
  `fieldcode` varchar(50) NOT NULL default '',
  `descr` varchar(100) NOT NULL default '',
  `type` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`inquiryfilterkey`),
  UNIQUE KEY `iifinquiryfilter` (`inquirycode`,`fieldcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `inquirygridsetting` (
  `inquirygridsettingkey` int(11) unsigned NOT NULL auto_increment,
  `inquirycode` varchar(20) NOT NULL default '',
  `fieldcode` varchar(50) NOT NULL default '',
  `displayname` varchar(100) NOT NULL default '',
  `visible` enum('Y','N') NOT NULL default 'Y',
  `width` int(11) NOT NULL default '80',
  `displayformat` varchar(20) NOT NULL,
  PRIMARY KEY  (`inquirygridsettingkey`),
  UNIQUE KEY `idinquirygridsetting` (`inquirycode`,`fieldcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `inquirymanager` (
  `inquirykey` int(11) unsigned NOT NULL auto_increment,
  `inquirycode` varchar(20) NOT NULL default '',
  `system` enum('Y','N') NOT NULL default 'N',
  `descr` varchar(100) NOT NULL default '',
  `beforecommand` mediumtext,
  `selectcommand` mediumtext,
  `aftercommand` mediumtext,
  `moduletype` int(11) NOT NULL default '-1',
  `pos` int(11) NOT NULL default '1',
  `formaname` varchar(50) NOT NULL default '',
  `fieldtypezoomin` varchar(50) NOT NULL default '',
  `fieldcodezoomin` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`inquirykey`),
  UNIQUE KEY `iimcodesystem` (`inquirycode`,`system`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `internalpr` (
  `internalprkey` int(11) unsigned NOT NULL auto_increment,
  `internalprcode` varchar(20) NOT NULL default '',
  `internalprdate` date default NULL,
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `remark5` varchar(100) NOT NULL,
  `remark6` varchar(100) NOT NULL,
  `remark7` varchar(100) NOT NULL,
  `remark8` varchar(100) NOT NULL,
  `globaldepartment` enum('Y','N') NOT NULL default 'Y',
  `departmentcode` varchar(20) NOT NULL default '',
  `globaljob` enum('Y','N') NOT NULL default 'N',
  `jobcode` varchar(20) NOT NULL default '',
  `globalstocklocation` enum('Y','N') NOT NULL default 'Y',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `closed` enum('Y','N') NOT NULL default 'N',
  `itemformatkey` int(11) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `approval` enum('Y','N') NOT NULL default 'N',
  `approvalby` varchar(20) NOT NULL,
  `approvaldatetime` datetime default NULL,
  `approvalremark` mediumtext,
  `ref1` varchar(30) NOT NULL,
  `ref2` varchar(30) NOT NULL,
  `ref3` varchar(30) NOT NULL,
  `ref4` varchar(30) NOT NULL,
  `approval2` enum('Y','N') NOT NULL default 'N',
  `approvalby2` varchar(20) NOT NULL,
  `approvaldatetime2` datetime default NULL,
  `approvalremark2` mediumtext,
  `approval3` enum('Y','N') NOT NULL default 'N',
  `approvalby3` varchar(20) NOT NULL,
  `approvaldatetime3` datetime default NULL,
  `approvalremark3` mediumtext,
  `closedremark` varchar(200) default NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`internalprkey`),
  UNIQUE KEY `iipcode` (`internalprcode`),
  KEY `iiprdate` (`internalprdate`),
  KEY `iiprstaffcode` (`staffcode`),
  KEY `iiprrefno` (`refno`),
  KEY `iiprdepartmentcode` (`departmentcode`),
  KEY `iiprstocklocationcode` (`stocklocationcode`),
  KEY `iiprareacode` (`areacode`),
  KEY `iiprapproval` (`approval`),
  KEY `iiprclosed` (`closed`),
  KEY `iiprapproval2` (`approval2`),
  KEY `iiprapproval3` (`approval3`),
  KEY `iiprjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `internalprid` (
  `internalprkey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`internalprkey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `internalpritem` (
  `internalprkey` int(11) unsigned NOT NULL default '0',
  `internalprcode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `qty` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `barcode` varchar(30) NOT NULL default '',
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  PRIMARY KEY  (`internalprkey`,`pos`),
  UNIQUE KEY `iiprikeyid` (`internalprkey`,`id`),
  KEY `iipricode` (`internalprcode`),
  KEY `iipristockcode` (`stockcode`),
  KEY `iipridepartmentcode` (`departmentcode`),
  KEY `iipristocklocationcode` (`stocklocationcode`),
  KEY `iipribarcode` (`barcode`),
  KEY `iiprijobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `invoice` (
  `invoicekey` int(11) unsigned NOT NULL auto_increment,
  `invoicecode` varchar(20) NOT NULL default '',
  `invoicedate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `multi` enum('Y','N') NOT NULL default 'N',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `duedate` date default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `fromdoctype` varchar(5) NOT NULL default 'INV',
  `salesamount` decimal(20,2) NOT NULL default '0.00',
  `done` enum('Y','N') NOT NULL default 'N',
  `taxpaid` enum('Y','N') NOT NULL default 'N',
  `taxpaiddate` date default NULL,
  `taxpaidremark` varchar(200) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `localsalesamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `totaltaxableamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `totalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `totallocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  PRIMARY KEY  (`invoicekey`),
  UNIQUE KEY `iinvcode` (`invoicecode`),
  KEY `iinvdate` (`invoicedate`),
  KEY `iinvdebtorcode` (`debtorcode`),
  KEY `iinvacccode` (`acccode`),
  KEY `iinvrefno` (`refno`),
  KEY `iinvmulti` (`multi`),
  KEY `iinvtermcode` (`termcode`),
  KEY `iinvstaffcode` (`staffcode`),
  KEY `iinvdepartmentcode` (`departmentcode`),
  KEY `iinvfromdoctype` (`fromdoctype`),
  KEY `iinvareacode` (`areacode`),
  KEY `iinvcurcode` (`curcode`),
  KEY `iinvjobcode` (`jobcode`),
  KEY `iinvtaxpaid` (`taxpaid`),
  KEY `iinvcancelled` (`cancelled`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `invoicemulti` (
  `invoicekey` int(11) unsigned NOT NULL default '0',
  `invoicecode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `subamount` decimal(20,2) NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `nonsales` enum('Y','N') NOT NULL default 'N',
  `localsubamount` decimal(20,2) NOT NULL default '0.00',
  `amountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `localamountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxpercentage` decimal(20,2) default NULL,
  `taxacccode` varchar(20) NOT NULL default '',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxrefno` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`invoicekey`,`pos`),
  KEY `iinvmacccode` (`acccode`),
  KEY `iinvmcode` (`invoicecode`),
  KEY `iinvmdepartmentcode` (`departmentcode`),
  KEY `iinvnonsales` (`nonsales`),
  KEY `iinvmjobcode` (`jobcode`),
  KEY `iimgsttypecode` (`gsttypecode`),
  KEY `iimtaxacccode` (`taxacccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `invoiceob` (
  `invoicekey` int(11) unsigned NOT NULL auto_increment,
  `invoicecode` varchar(20) NOT NULL default '',
  `invoicedate` date default NULL,
  `debtorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `termcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `duedate` date default NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`invoicekey`),
  UNIQUE KEY `iinvobcode` (`invoicecode`),
  KEY `iinvobdate` (`invoicedate`),
  KEY `iinvobdebtorcode` (`debtorcode`),
  KEY `iinvobrefno` (`refno`),
  KEY `iinvobtermcode` (`termcode`),
  KEY `iinvobstaffcode` (`staffcode`),
  KEY `iinvobdepartmentcode` (`departmentcode`),
  KEY `iinvobareacode` (`areacode`),
  KEY `iinvobcurcode` (`curcode`),
  KEY `iinvobjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `irrsetup` (
  `irrsetupkey` int(11) unsigned NOT NULL auto_increment,
  `irrsetupcode` varchar(50) NOT NULL default '',
  `customgsttypekeylist` mediumtext,
  PRIMARY KEY  (`irrsetupkey`),
  UNIQUE KEY `iirrsetupcode` (`irrsetupcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `itemformat` (
  `itemformatkey` int(11) unsigned NOT NULL auto_increment,
  `formname` varchar(100) NOT NULL default '',
  `itemformatcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `descr` varchar(200) NOT NULL default '',
  `autowidth` enum('Y','N') NOT NULL default 'N',
  `reportcode` varchar(20) NOT NULL default '',
  `system` enum('Y','N') NOT NULL default 'N',
  `itemformatblob` mediumblob,
  PRIMARY KEY  (`itemformatkey`),
  UNIQUE KEY `iitfformnamecode` (`formname`,`itemformatcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `itemtemplate` (
  `stockkey` int(11) unsigned NOT NULL,
  `stockcode` varchar(30) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `itemstockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `qty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `unitpricebaseuom` double NOT NULL default '0',
  `amount` decimal(20,2) default NULL,
  `salestaxpercentage` decimal(20,2) default NULL,
  `salestaxamount` decimal(20,2) default NULL,
  `amountaftertax` decimal(20,2) default NULL,
  PRIMARY KEY (`stockkey`,`pos`),
  KEY `itpstockcode` (`stockcode`),
  KEY `itpitemstockcode` (`itemstockcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `itemtransferred` (
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `docid` int(11) unsigned NOT NULL default '0',
  `totype` varchar(5) NOT NULL default '',
  `tokey` int(11) unsigned NOT NULL default '0',
  `toid` int(11) unsigned NOT NULL default '0',
  `touom` varchar(20) NOT NULL default '',
  `touomrate` double NOT NULL default '1',
  `toqty` decimal(20,6) NOT NULL default '0.000000',
  `toqtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  PRIMARY KEY  (`doctype`,`dockey`,`docid`,`totype`,`tokey`,`toid`),
  KEY `iitftotypekeyid` (`totype`,`tokey`,`toid`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `job` (
  `jobkey` int(11) unsigned NOT NULL auto_increment,
  `jobcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `note` mediumtext,
  `level` tinyint(3) unsigned NOT NULL default '0',
  `rootkey` int(11) unsigned NOT NULL default '0',
  `parentkey` int(11) unsigned NOT NULL default '0',
  `linkkey` varchar(250) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `debtorcode` varchar(20) NOT NULL default '',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `statuscode` varchar(20) NOT NULL default '',
  `manpoweracccode` varchar(20) NOT NULL default '',
  `startdate` date default NULL,
  `duedate` date default NULL,
  `actualenddate` date default NULL,
  `remark1` varchar(200) NOT NULL default '',
  `remark2` varchar(200) NOT NULL default '',
  `remark3` varchar(200) NOT NULL default '',
  `remark4` varchar(200) NOT NULL default '',
  `remark5` varchar(200) NOT NULL default '',
  `remark6` varchar(200) NOT NULL default '',
  `remark7` varchar(200) NOT NULL default '',
  `remark8` varchar(200) NOT NULL default '',
  `contractamount` decimal(20,2) NOT NULL default '0.00',
  `manpoweramount` decimal(20,2) NOT NULL default '0.00',
  `pettycashamount` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`jobkey`),
  UNIQUE KEY `ijobcode` (`jobcode`),
  KEY `ijoblevel` (`level`),
  KEY `ijobrootkey` (`rootkey`),
  KEY `ijobparentkey` (`parentkey`),
  KEY `ijoblinkkey` (`linkkey`),
  KEY `ijobinactive` (`inactive`),
  KEY `ijobdebtorcode` (`debtorcode`),
  KEY `ijobstaffcode` (`staffcode`),
  KEY `ijobareacode` (`areacode`),
  KEY `ijobstatuscode` (`statuscode`),
  KEY `ijobmanpoweracccode` (`manpoweracccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `joborder` (
  `joborderkey` int(11) unsigned NOT NULL auto_increment,
  `jobordercode` varchar(20) NOT NULL default '',
  `joborderdate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `stockbomcode` varchar(20) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `qty` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `salesordercode` varchar(20) NOT NULL default '',
  `salesorderid` int(11) unsigned NOT NULL default '0',
  `debtorcode` varchar(20) NOT NULL default '',
  `deliverydate` datetime default NULL,
  `note` mediumtext,
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `itemformatkey` int(11) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `debtorname` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`joborderkey`),
  UNIQUE KEY `ijocode` (`jobordercode`),
  KEY `ijodate` (`joborderdate`),
  KEY `ijostockbomcode` (`stockbomcode`),
  KEY `ijostockcode` (`stockcode`),
  KEY `ijorefno` (`refno`),
  KEY `ijosalesordercodeid` (`salesordercode`,`salesorderid`),
  KEY `ijodebtorcode` (`debtorcode`),
  KEY `ijodeliverydate` (`deliverydate`),
  KEY `ijocancelled` (`cancelled`),
  KEY `ijodepartmentcode` (`departmentcode`),
  KEY `ijojobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `joborderid` (
  `joborderkey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`joborderkey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `joborderitem` (
  `joborderkey` int(11) unsigned NOT NULL default '0',
  `jobordercode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `stockbomcode` varchar(20) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `qty` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `bomrate` double NOT NULL default '1',
  `batchno` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`joborderkey`,`pos`),
  UNIQUE KEY `ijoikeyid` (`joborderkey`,`id`),
  KEY `ijoicode` (`jobordercode`),
  KEY `ijoistockbomcode` (`stockbomcode`),
  KEY `ijoistockcode` (`stockcode`),
  KEY `ijoibatchno` (`batchno`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `joborderpacking` (
  `joborderpackingkey` int(11) unsigned NOT NULL auto_increment,
  `joborderkey` int(11) unsigned NOT NULL default '0',
  `jobordercode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `packingqty` decimal(20,6) default NULL,
  `packinguom` varchar(20) NOT NULL default '',
  `packingnetweight` decimal(20,6) default NULL,
  `packinggrossweight` decimal(20,6) default NULL,
  `qtyperpack` decimal(20,6) default NULL,
  `totalqty` decimal(20,6) default NULL,
  `weightofpallet` decimal(20,6) default NULL,
  PRIMARY KEY  (`joborderpackingkey`),
  KEY `ijopcode` (`jobordercode`),
  KEY `ijopjoborderkeypos` (`joborderkey`,`pos`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `joborderpallet` (
  `joborderpalletkey` int(11) unsigned NOT NULL auto_increment,
  `joborderpackingkey` int(11) unsigned NOT NULL default '0',
  `joborderkey` int(11) unsigned NOT NULL default '0',
  `jobordercode` varchar(20) NOT NULL default '',
  `palletno` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`joborderpalletkey`),
  KEY `ijopljoborderpackingkey` (`joborderpackingkey`),
  KEY `ijoplcode` (`jobordercode`),
  KEY `ijopljoborderkey` (`joborderkey`),
  KEY `ijoplpalletno` (`palletno`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `jobordertime` (
  `joborderkey` int(11) unsigned NOT NULL default '0',
  `jobordercode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `processdate` date default NULL,
  `processbegin` time default NULL,
  `processend` time default NULL,
  `qty` decimal(20,6) default NULL,
  `operator` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`joborderkey`,`pos`),
  KEY `ijotcode` (`jobordercode`),
  KEY `ijotdate` (`processdate`),
  KEY `ijotbeginend` (`processbegin`,`processend`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `jobsheet` (
  `jobsheetkey` int(11) unsigned NOT NULL auto_increment,
  `jobsheetcode` varchar(20) NOT NULL default '',
  `jobsheetdate` date default NULL,
  `requiredate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `remark5` varchar(100) NOT NULL,
  `remark6` varchar(100) NOT NULL,
  `remark7` varchar(100) NOT NULL,
  `remark8` varchar(100) NOT NULL,
  `departmentcode` varchar(20) NOT NULL default '',
  `globaljob` enum('Y','N') NOT NULL default 'N',
  `jobcode` varchar(20) NOT NULL default '',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `itemformatkey` int(11) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `globaldepartment` enum('Y','N') NOT NULL default 'Y',
  `globalstocklocation` enum('Y','N') NOT NULL default 'Y',
  PRIMARY KEY  (`jobsheetkey`),
  UNIQUE KEY `ijscode` (`jobsheetcode`),
  KEY `ijsdate` (`jobsheetdate`),
  KEY `ijsrefno` (`refno`),
  KEY `ijsdepartmentcode` (`departmentcode`),
  KEY `ijsstocklocationcode` (`stocklocationcode`),
  KEY `ijscancelled` (`cancelled`),
  KEY `ijsjobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `jobsheetid` (
  `jobsheetkey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`jobsheetkey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `jobsheetitem` (
  `jobsheetkey` int(11) unsigned NOT NULL default '0',
  `jobsheetcode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` mediumtext,
  `qtybaseuom` decimal(20,6) default NULL,
  `barcode` varchar(30) NOT NULL default '',
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `taskcode` varchar(20) NOT NULL default '',
  `taskname` varchar(200) NOT NULL default '',
  `productionnote` mediumtext,
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default NULL,
  `fromdocid` int(11) unsigned default NULL,
  `fromdoccode` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`jobsheetkey`,`pos`),
  UNIQUE KEY `ijsikeyid` (`jobsheetkey`,`id`),
  KEY `ijsicode` (`jobsheetcode`),
  KEY `ijsistockcode` (`stockcode`),
  KEY `ijsibarcode` (`barcode`),
  KEY `ijiifromdoctypekeypos` (`fromdoctype`,`fromdockey`,`fromdocid`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `jobsheetlabour` (
  `jobsheetkey` int(11) unsigned NOT NULL default '0',
  `jobsheetitemid` int(11) unsigned NOT NULL default '0',
  `jobsheettaskitemid` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `labourcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` mediumtext,
  `qtybaseuom` decimal(20,6) default NULL,
  PRIMARY KEY  (`jobsheetkey`,`jobsheetitemid`, `jobsheettaskitemid`, `pos`),
  UNIQUE KEY `ijlkeyid` (`jobsheetkey`, `jobsheetitemid`, `jobsheettaskitemid`, `id`),
  KEY `ijlcode` (`labourcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `jobsheetlabourcompleted` (
  `jobsheetlabourcompletedkey` int(11) unsigned NOT NULL auto_increment,
  `jobsheetkey` int(11) unsigned NOT NULL default '0',
  `labourkey` int(11) unsigned NOT NULL default '0',
  `completeddatetime` datetime default NULL,
  PRIMARY KEY  (`jobsheetlabourcompletedkey`),
  UNIQUE KEY  (`jobsheetkey`,`labourkey`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `jobsheettaskitem` (
  `jobsheetkey` int(11) unsigned NOT NULL default '0',
  `jobsheetitemid` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `taskitemcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` mediumtext,
  `labourcost` decimal(20,6) NOT NULL default '0.00',
  PRIMARY KEY  (`jobsheetkey`,`jobsheetitemid`, `pos`),
  UNIQUE KEY `ijstikeyid` (`jobsheetkey`, `jobsheetitemid`, `id`),
  KEY `ijsticode` (`taskitemcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `journal` (
  `journalkey` int(11) unsigned NOT NULL auto_increment,
  `journalcode` varchar(20) NOT NULL default '',
  `journaldate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL,
  `totaldebit` decimal(20,2) NOT NULL default '0.00',
  `totalcredit` decimal(20,2) NOT NULL default '0.00',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default '0',
  `journalbooktypecode` varchar(20) NOT NULL default '',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `totaltaxablelocaldebit` decimal(20,2) NOT NULL default '0.00',
  `totaltaxablelocalcredit` decimal(20,2) NOT NULL default '0.00',
  `totaltaxlocaldebit` decimal(20,2) NOT NULL default '0.00',
  `totaltaxlocalcredit` decimal(20,2) NOT NULL default '0.00',
  `totaltaxadjustment` decimal(20,2) NOT NULL default '0.00',
  `totallocaldebitaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `totallocalcreditaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`journalkey`),
  UNIQUE KEY `ijncode` (`journalcode`),
  KEY `ijndate` (`journaldate`),
  KEY `ijncancelled` (`cancelled`),
  KEY `ijnJournalBookTypeCode` (`journalbooktypecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `journalbooktype` (
  `journalbooktypekey` int(11) unsigned NOT NULL auto_increment,
  `journalbooktypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `issystem` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`journalbooktypekey`),
  UNIQUE KEY `ijbtcode` (`journalbooktypecode`),
  KEY `ijbtinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `journalmulti` (
  `journalkey` int(11) unsigned NOT NULL default '0',
  `journalcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `descr2` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `refno2` varchar(20) NOT NULL default '',
  `debit` decimal(20,2) NOT NULL default '0.00',
  `credit` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localdebit` decimal(20,2) NOT NULL default '0.00',
  `localcredit` decimal(20,2) NOT NULL default '0.00',
  `postingdate` date default NULL,
  `remark1` varchar(200) NOT NULL default '',
  `remark2` varchar(200) NOT NULL default '',
  `printtype` enum('J','R','P') NOT NULL default 'J',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxpercentage` decimal(20,2) default NULL,
  `taxacccode` varchar(20) NOT NULL default '',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxablelocaldebit` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalcredit` decimal(20,2) NOT NULL default '0.00',
  `taxlocaldebit` decimal(20,2) NOT NULL default '0.00',
  `taxlocalcredit` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `localdebitaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localcreditaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `totallocaldebit` decimal(20,2) NOT NULL default '0.00',
  `totallocalcredit` decimal(20,2) NOT NULL default '0.00',
  `taxpermitno` varchar(50) NOT NULL default '',
  `taxexportcountrycode` varchar(50) NOT NULL default '',
  `taxrefno` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`journalkey`,`pos`),
  KEY `ijnmacccode` (`acccode`),
  KEY `ijnmcode` (`journalcode`),
  KEY `ijnmdepartmentcode` (`departmentcode`),
  KEY `ijnmcurcode` (`curcode`),
  KEY `ijnmjobcode` (`jobcode`),
  KEY `ijnmstaffcode` (`staffcode`),
  KEY `ijnmgsttypecode` (`gsttypecode`),
  KEY `ijnmtaxacccode` (`taxacccode`),
  KEY `ijnmtaxexportcountrycode` (`taxexportcountrycode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `labour` (
  `labourkey` int(11) unsigned NOT NULL auto_increment,
  `labourcode` varchar(20) NOT NULL default '',
  `name` varchar(200) NOT NULL default '',
  `name2` varchar(200) NOT NULL default '',
  `gender` enum('MALE','FEMALE','UNKNOW') NOT NULL default 'UNKNOW',
  `dob` date default NULL,
  `photo` mediumblob,
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `phone2` varchar(30) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `icno` varchar(30) NOT NULL default '',
  `epfno` varchar(30) NOT NULL default '',
  `sosocno` varchar(30) NOT NULL default '',
  `taxno` varchar(30) NOT NULL default '',
  `datejoined` date default NULL,
  `dateleft` date default NULL,
  `note` mediumtext,
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`labourkey`),
  UNIQUE KEY `iscode` (`labourcode`),
  KEY `ilicno` (`icno`),
  KEY `ilinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `labourcostadjustment` (
  `labourcostadjustmentkey` int(11) unsigned NOT NULL auto_increment,
  `labourcode` varchar(20) NOT NULL default '',
  `adjustmentdate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(50) NOT NULL default '',
  `adjustmentcost` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`labourcostadjustmentkey`),
  KEY `ilcalabourcode` (`labourcode`),
  KEY `ilcaadjustmentdate` (`adjustmentdate`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `labourot` (
  `labourotkey` int(11) unsigned NOT NULL auto_increment,
  `labourcode` varchar(20) NOT NULL default '',
  `otdate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `othour` decimal(20,2) NOT NULL default '0.00',
  `otcostperhour` decimal(20,4) NOT NULL default '0.00',
  `otcost` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  (`labourotkey`),
  KEY `ilotlabourcode` (`labourcode`),
  KEY `ilotdate` (`otdate`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `layoutadvsearch` (
  `formname` varchar(50) NOT NULL default '',
  `usercode` varchar(20) NOT NULL default '',
  `layout` mediumblob,
  `memo` text,
  `searchtext1` varchar(200) NOT NULL default '',
  `searchtext2` varchar(200) NOT NULL default '',
  `searchfield1` varchar(200) NOT NULL default '',
  `searchfield2` varchar(200) NOT NULL default '',
  `filtertext1` varchar(200) NOT NULL default '',
  `filtertext2` varchar(200) NOT NULL default '',
  `filterfield1` varchar(200) NOT NULL default '',
  `filterfield2` varchar(200) NOT NULL default '',
  `loaddata` enum('Y','N') NOT NULL default 'Y',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`formname`,`usercode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `layoutgrid` (
  `formname` varchar(100) NOT NULL default '',
  `gridname` varchar(100) NOT NULL default '',
  `usercode` varchar(20) NOT NULL default '',
  `layout` mediumblob,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`formname`, `gridname`,`usercode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `locationdiscountbook` (
  `locationdiscountbookkey` int(11) unsigned NOT NULL auto_increment,
  `locationdiscountbookcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `promotion` enum('Y','N') NOT NULL default 'N',
  `datefrom` date default Null,
  `dateto` date default Null,
  `disablememberdiscount` enum('Y','N') NOT NULL default 'Y',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`locationdiscountbookkey`),
  UNIQUE KEY `ildblocationdiscountbookcode` (`locationdiscountbookcode`),
  KEY `ildbstocklocationcode` (`stocklocationcode`),
  KEY `ildbpromotion` (`promotion`),
  KEY `ildbdatefromto` (`datefrom`,`dateto`),
  KEY `ildbdateto` (`dateto`),
  KEY `ildbinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `locationdiscountbookitem` (
  `locationdiscountbookkey` int(11) unsigned NOT NULL default '0',
  `locationdiscountbookcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `pricecategorycode` varchar(20) NOT NULL default '',
  `membertypecode` varchar(20) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `minimumqty` decimal(20,6) default NULL,
  `discountformat` varchar(100) default NULL,
  PRIMARY KEY  (`locationdiscountbookkey`,`pos`),
  UNIQUE KEY `ildbicodepos` (`locationdiscountbookcode`,`pos`),
  KEY `ildbipricecategorycode` (`pricecategorycode`),
  KEY `ildbimembertypecode` (`membertypecode`),
  KEY `ildbistockcode` (`stockcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `locationpricebook` (
  `locationpricebookkey` int(11) unsigned NOT NULL auto_increment,
  `locationpricebookcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `promotion` enum('Y','N') NOT NULL default 'N',
  `datefrom` date default Null,
  `dateto` date default Null,
  `disablememberdiscount` enum('Y','N') NOT NULL default 'Y',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`locationpricebookkey`),
  UNIQUE KEY `ilpblocationpricebookcode` (`locationpricebookcode`),
  KEY `ilpbstocklocationcode` (`stocklocationcode`),
  KEY `ilpbpromotion` (`promotion`),
  KEY `ilpbdatefromto` (`datefrom`,`dateto`),
  KEY `ilpbdateto` (`dateto`),
  KEY `ilpbinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `locationpricebookitem` (
  `locationpricebookkey` int(11) unsigned NOT NULL default '0',
  `locationpricebookcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `pricecategorycode` varchar(20) NOT NULL default '',
  `membertypecode` varchar(20) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `minimumqty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) NOT NULL default '0.000000',
  PRIMARY KEY  (`locationpricebookkey`,`pos`),
  UNIQUE KEY `ilpbicodepos` (`locationpricebookcode`,`pos`),
  KEY `ilpbipricecategorycode` (`pricecategorycode`),
  KEY `ilpbimembertypecode` (`membertypecode`),
  KEY `ilpbistockcode` (`stockcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `loginevent` (
  `logineventid` bigint(20) unsigned NOT NULL auto_increment,
  `connectionid` bigint(20) unsigned NOT NULL default '0',
  `start` datetime default NULL,
  `finished` datetime default NULL,
  `timeout` int(10) unsigned NOT NULL default '60',
  `closed` enum('Y','N') NOT NULL default 'N',
  `dbuser` varchar(250) NOT NULL default '',
  `sysuser` varchar(250) NOT NULL default '',
  `hostipaddress` varchar(50) NOT NULL default '',
  `syskey` blob,
  PRIMARY KEY  (`logineventid`),
  KEY `ileclosed` (`closed`),
  KEY `iledbuser` (`dbuser`),
  KEY `ilesysuser` (`sysuser`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `matrixcolor` (
  `matrixcolorkey` int(11) unsigned NOT NULL auto_increment,
  `matrixcolorcode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `autocode` char(2) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`matrixcolorkey`),
  UNIQUE KEY `imxcmatrixcolorcode` (`matrixcolorcode`),
  UNIQUE KEY `imxcautocode` (`autocode`),
  KEY `imxcinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `matrixitem` (
  `matrixitemkey` int(11) unsigned NOT NULL auto_increment,
  `matrixitemcode` varchar(20) NOT NULL default '',
  `name` varchar(200) NOT NULL default '',
  `stockcodeprefix` varchar(20) NOT NULL default '',
  `barcodeprefix` varchar(20) NOT NULL default '',
  `baseuom` varchar(20) NOT NULL default '',
  `stockcategorycode` varchar(20) NOT NULL default '',
  `stockgroupcode` varchar(20) NOT NULL default '',
  `stockclasscode` varchar(20) NOT NULL default '',
  `stockbrandcode` varchar(20) NOT NULL default '',
  `stockmodelcode` varchar(20) NOT NULL default '',
  `stocksize` varchar(20) NOT NULL default '',
  `stockcolor` varchar(20) NOT NULL default '',
  `stockweight` varchar(20) NOT NULL default '',
  `stockref1` varchar(20) NOT NULL default '',
  `stockref2` varchar(20) NOT NULL default '',
  `stockref3` varchar(20) NOT NULL default '',
  `stockref4` varchar(20) NOT NULL default '',
  `stockref5` varchar(20) NOT NULL default '',
  `stockref6` varchar(20) NOT NULL default '',
  `stockref7` varchar(20) NOT NULL default '',
  `stockref8` varchar(20) NOT NULL default '',
  `service` enum('Y','N') NOT NULL default 'N',
  `serialnocontrol` enum('Y','N') NOT NULL default 'N',
  `salesacccode` varchar(20) NOT NULL,
  `salesreturnacccode` varchar(20) NOT NULL,
  `purchasesacccode` varchar(20) NOT NULL,
  `purchasesreturnacccode` varchar(20) NOT NULL,
  `note` mediumtext,
  `batchnocontrol` enum('Y','N') NOT NULL default 'N',
  `unsalesableperiod` int(11) unsigned NOT NULL default '0',
  `validityperiod` int(11) unsigned NOT NULL default '0',
  `done` enum('Y','N') NOT NULL default 'N',
  `stockadjustmentacccode` varchar(20) NOT NULL,
  `stockissueacccode` varchar(20) NOT NULL,
  `stockreceivedacccode` varchar(20) NOT NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`matrixitemkey`),
  UNIQUE KEY `imximatrixitemcode` (`matrixitemcode`),
  UNIQUE KEY `imxistockcodeprefix` (`stockcodeprefix`),
  KEY `imxibarcodeprefix` (`barcodeprefix`),
  KEY `imxicategorycode` (`stockcategorycode`),
  KEY `imxigroupcode` (`stockgroupcode`),
  KEY `imxiclasscode` (`stockclasscode`),
  KEY `imxibrandcode` (`stockbrandcode`),
  KEY `imximodelcode` (`stockmodelcode`),
  KEY `imxisalesacccode` (`salesacccode`),
  KEY `imxisalesreturnacccode` (`salesreturnacccode`),
  KEY `imxipurchasesacccode` (`purchasesacccode`),
  KEY `imxipurchasesreturnacccode` (`purchasesreturnacccode`),
  KEY `imxibatchnocontrol` (`batchnocontrol`),
  KEY `imxistockadjustmentacccode` (`stockadjustmentacccode`),
  KEY `imxistockissueacccode` (`stockissueacccode`),
  KEY `imxistockreceivedacccode` (`stockreceivedacccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `matrixitemcolor` (
  `matrixitemkey` int(11) unsigned NOT NULL,
  `matrixitemcode` varchar(20) NOT NULL default '',
  `matrixcolorcode` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`matrixitemkey`,`matrixcolorcode`),
  UNIQUE KEY `imxiccodecolor` (`matrixitemcode`,`matrixcolorcode`),
  KEY `imxicmatrixcolorcode` (`matrixcolorcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `matrixitemsize` (
  `matrixitemkey` int(11) unsigned NOT NULL,
  `matrixitemcode` varchar(20) NOT NULL default '',
  `matrixsizecode` varchar(20) NOT NULL default '',
  PRIMARY KEY  (`matrixitemkey`,`matrixsizecode`),
  UNIQUE KEY `imxiscodesize` (`matrixitemcode`,`matrixsizecode`),
  KEY `imxismatrixsizecode` (`matrixsizecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `matrixsize` (
  `matrixsizekey` int(11) unsigned NOT NULL auto_increment,
  `matrixsizecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `autocode` char(2) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`matrixsizekey`),
  UNIQUE KEY `imxsmatrixsizecode` (`matrixsizecode`),
  UNIQUE KEY `imxsautocode` (`autocode`),
  KEY `imxsinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `member` (
  `memberkey` int(11) unsigned NOT NULL auto_increment,
  `membercode` varchar(50) NOT NULL default '',
  `name` varchar(200) NOT NULL default '',
  `name2` varchar(200) NOT NULL default '',
  `gender` enum('MALE','FEMALE','UNKNOW') NOT NULL default 'UNKNOW',
  `dob` date default NULL,
  `photo` mediumblob,
  `address1` varchar(200) NOT NULL default '',
  `address2` varchar(200) NOT NULL default '',
  `address3` varchar(200) NOT NULL default '',
  `address4` varchar(200) NOT NULL default '',
  `phone` varchar(30) NOT NULL default '',
  `phone2` varchar(30) NOT NULL default '',
  `email` varchar(200) NOT NULL default '',
  `icno` varchar(30) NOT NULL default '',
  `occupation` varchar(30) NOT NULL default '',
  `membertypecode` varchar(20) NOT NULL default '',
  `datejoined` date default NULL,
  `dateleft` date default NULL,
  `note` mediumtext,
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastrenew` date default NULL,
  `validmemberyear` int(11) unsigned NOT NULL default '1',
  `dateexpired` date default NULL,
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`memberkey`),
  UNIQUE KEY `imbcode` (`membercode`),
  KEY `imbicno` (`icno`),
  KEY `imbinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memberdebitcardbonus` (
  `debitcardamount` decimal(20,2) NOT NULL default '0.00',
  `debitcardbonus` decimal(20,2) NOT NULL default '0.00',
  `inactive` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`debitcardamount`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memberdebitcardtranc` (
  `memberdebitcardtranckey` int(11) unsigned NOT NULL auto_increment,
  `memberdebitcardtranccode` varchar(20) NOT NULL default '',
  `memberdebitcardtrancdate` datetime default NULL,
  `membercode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `descr2` varchar(200) NOT NULL default '',
  `note` mediumtext,
  `countercode` varchar(20) NOT NULL default '',
  `cashiercode` varchar(20) NOT NULL default '',
  `supervisorcode` varchar(20) NOT NULL default '',
  `debitcardamount` decimal(20,2) NOT NULL default '0.00',
  `debitcardbonusinpercent` decimal(20,2) NOT NULL default '0.00',
  `debitcardbonusamount` decimal(20,2) NOT NULL default '0.00',
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default NULL,
  `fromdoccode` varchar(20) NOT NULL default '',
  `clientcreated` datetime default NULL,
  `clientmodified` datetime default NULL,
  PRIMARY KEY  (`memberdebitcardtranckey`),
  UNIQUE KEY `imdctcode` (`memberdebitcardtranccode`),
  KEY `imdctdate` (`memberdebitcardtrancdate`),
  KEY `imdctcountercode` (`countercode`),
  KEY `imdctcashiercode` (`cashiercode`),
  KEY `imdctmembercode` (`membercode`),
  KEY `imdctclientcreated` (`clientcreated`),
  KEY `imdctclientmodified` (`clientmodified`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memberpointadjustment` (
  `memberpointadjustmentkey` int(11) unsigned NOT NULL auto_increment,
  `memberpointadjustmentcode` varchar(20) NOT NULL default '',
  `memberpointadjustmentdate` datetime default NULL,
  `membercode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(200) NOT NULL default '',
  `note` mediumtext,
  `pointadjustment` decimal(20,2) NOT NULL default '0.00',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `batchno` varchar(20) NOT NULL default '',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`memberpointadjustmentkey`),
  UNIQUE KEY `imdctcode` (`memberpointadjustmentcode`),
  KEY `impamembercode` (`membercode`),
  KEY `impabatchno` (`batchno`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memberpointob` (
  `membercode` varchar(50) NOT NULL default '',
  `obdate` date default NULL,
  `point` decimal(20,2) NOT NULL default '0.00',
  `descr` varchar(200) NOT NULL default '',
  PRIMARY KEY  USING BTREE (`membercode`),
  KEY `impobmembercode` (`membercode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memberpointredemption` (
  `memberpointredemptionkey` int(11) unsigned NOT NULL auto_increment,
  `memberpointredemptioncode` varchar(20) NOT NULL default '',
  `memberpointredemptiondate` date default NULL,
  `membercode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `pointpayment` decimal(20,2) default NULL,
  `note` mediumtext,
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `remark5` varchar(100) NOT NULL,
  `remark6` varchar(100) NOT NULL,
  `remark7` varchar(100) NOT NULL,
  `remark8` varchar(100) NOT NULL,
  `globaldepartment` enum('Y','N') NOT NULL default 'Y',
  `departmentcode` varchar(20) NOT NULL default '',
  `globaljob` enum('Y','N') NOT NULL default 'N',
  `jobcode` varchar(20) NOT NULL default '',
  `globalstocklocation` enum('Y','N') NOT NULL default 'Y',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `itemformatkey` int(11) unsigned NOT NULL default '0',
  `created` datetime default NULL,
  `modified` datetime default NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `closed` enum('Y','N') NOT NULL default 'N',
  `closedremark` varchar(200) default NULL,
  `stockissuekey` int(11) unsigned default NULL,
  PRIMARY KEY  (`memberpointredemptionkey`),
  UNIQUE KEY `imprcode` (`memberpointredemptioncode`),
  KEY `imprdate` (`memberpointredemptiondate`),
  KEY `imprrefno` (`refno`),
  KEY `imprdepartmentcode` (`departmentcode`),
  KEY `imprstocklocationcode` (`stocklocationcode`),
  KEY `imprcancelled` (`cancelled`),
  KEY `imprclosed` (`closed`),
  KEY `imprjobcode` (`jobcode`),
  KEY `imprstockissuekey` (`stockissuekey`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memberpointredemptionid` (
  `memberpointredemptionkey` int(11) unsigned NOT NULL default '0',
  `id` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`memberpointredemptionkey`,`id`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memberpointredemptionitem` (
  `memberpointredemptionkey` int(11) unsigned NOT NULL default '0',
  `memberpointredemptioncode` varchar(20) NOT NULL default '',
  `id` int(11) unsigned NOT NULL default '0',
  `pos` int(11) unsigned NOT NULL default '0',
  `itemno` varchar(10) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `moredescr` text,
  `qty` decimal(20,6) default NULL,
  `unitprice` decimal(20,6) default NULL,
  `uom` varchar(20) NOT NULL default '',
  `uomrate` double NOT NULL default '1',
  `qtybaseuom` decimal(20,6) NOT NULL default '0.000000',
  `unitpricebaseuom` double NOT NULL default '0',
  `amount` decimal(20,2) default NULL,
  `itempointpayment` decimal(20,2) NOT NULL default '0.00',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `stocklocationcode` varchar(20) NOT NULL default '',
  `barcode` varchar(30) NOT NULL default '',
  `remark1` varchar(100) NOT NULL,
  `remark2` varchar(100) NOT NULL,
  `remark3` varchar(100) NOT NULL,
  `remark4` varchar(100) NOT NULL,
  `serialno` text,
  `batchno` varchar(30) NOT NULL default '',
  PRIMARY KEY  (`memberpointredemptionkey`,`pos`),
  UNIQUE KEY `imprikeyid` (`memberpointredemptionkey`,`id`),
  KEY `impricode` (`memberpointredemptioncode`),
  KEY `impristockcode` (`stockcode`),
  KEY `impridepartmentcode` (`departmentcode`),
  KEY `impristocklocationcode` (`stocklocationcode`),
  KEY `impribarcode` (`barcode`),
  KEY `impribatchno` (`batchno`),
  KEY `imprijobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memberpointtrans` (
  `memberpointtranskey` int(11) unsigned NOT NULL auto_increment,
  `memberpointtransdate` date default NULL,
  `descr` varchar(200) NOT NULL default '',
  `membercode` varchar(50) NOT NULL default '',
  `point` decimal(20,2) NOT NULL default '0.00',
  `doctype` varchar(5) NOT NULL default '',
  `doccode` varchar(20) NOT NULL default '',
  `expirydate` date default NULL,
  PRIMARY KEY  (`memberpointtranskey`),
  KEY `imptdate` (`memberpointtransdate`),
  KEY `imptacccode` (`membercode`),
  KEY `imptdoctypedoccode` (`doctype`,`doccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `membertype` (
  `membertypekey` int(11) unsigned NOT NULL auto_increment,
  `membertypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `discount` decimal(20,2) NOT NULL default '0.00',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`membertypekey`),
  UNIQUE KEY `imtcode` (`membertypecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memotemplate` (
  `memotemplatekey` int(11) unsigned NOT NULL auto_increment,
  `memotemplatecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `memo` mediumtext,
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`memotemplatekey`),
  UNIQUE KEY `imtcode` (`memotemplatecode`),
  KEY `imtinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `memotype` (
  `memotypekey` int(11) unsigned NOT NULL auto_increment,
  `memotypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`memotypekey`),
  UNIQUE KEY `isttscode` (`memotypecode`),
  KEY `imtpinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `metermultiprice` (
  `metermultipricekey` int(11) unsigned NOT NULL auto_increment,
  `metermultipricecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`metermultipricekey`),
  UNIQUE KEY `icmbaode` (`metermultipricecode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `metermultipriceitem` (
  `metermultipricecode` varchar(20) NOT NULL default '',
  `meterreading` decimal(20,2) NOT NULL default '0.00',
  `unitprice` decimal(20,6) NOT NULL default '0.00',
  PRIMARY KEY  (`metermultipricecode`,`meterreading`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `metertype` (
  `metertypekey` int(11) unsigned NOT NULL auto_increment,
  `metertypecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `stockcode` varchar(30) NOT NULL default '',
  `minimumcharges` decimal(20,2) NOT NULL default '0.00',
  `chargesrate` decimal(20,6) NOT NULL default '0.000000',
  `metermultipricecode` varchar(20) NOT NULL default '',
  `rebateqtyinpercent` decimal(20,2) NOT NULL default '0.00',
  `focqty` decimal(20,2) NOT NULL default '0.00',
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`metertypekey`),
  UNIQUE KEY `imtcode` (`metertypecode`),
  KEY `imtstockcode` (`stockcode`),
  KEY `imtinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `msic` (
  `msickey` int(11) unsigned NOT NULL auto_increment,
  `msiccode` varchar(20) NOT NULL default '',
  `descr` text,
  `msicgroup` text,
  `msiccategory` text,
  PRIMARY KEY  (`msickey`),
  UNIQUE KEY `iasacode` (`msiccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `notenotification` (
  `notenotificationkey` int(11) unsigned NOT NULL auto_increment,
  `senttime` datetime default NULL,
  `subject` varchar(200) NOT NULL default '',
  `message` mediumtext,
  `usercodefrom` varchar(20) NOT NULL default '',
  `usercodeto` varchar(20) NOT NULL default '',
  `readstatus` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`notenotificationkey`),
  KEY `innusercodefrom` (`usercodefrom`),
  KEY `innusercodeto` (`usercodeto`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `notetemplate` (
  `notetemplatekey` int(11) unsigned NOT NULL auto_increment,
  `notetemplatecode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `note` mediumtext,
  `inactive` enum('Y','N') NOT NULL default 'N',
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`notetemplatekey`),
  UNIQUE KEY `intcode` (`notetemplatecode`),
  KEY `intinactive` (`inactive`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `partialexemptionadjustment` (
  `partialexemptionadjustmentkey` int(11) unsigned NOT NULL auto_increment,
  `partialexemptionadjustmentcode` varchar(20) NOT NULL default '',
  `partialexemptionadjustmentdate` date default NULL,
  `taxperiodstartdate` date default NULL,
  `taxperiodenddate` date default NULL,
  `dmr` decimal(20,2) NOT NULL default '0.00',
  `dmrstatus` enum('None','Qualify', 'No Qualify') NOT NULL default 'None',
  `t` decimal(20,2) NOT NULL default '0.00',
  `e` decimal(20,2) NOT NULL default '0.00',
  `o` decimal(20,2) NOT NULL default '0.00',
  `irr` decimal(20,2) NOT NULL default '0.00',
  `localtotaltxregstclaimable` decimal(20,2) NOT NULL default '0.00',
  `localtotaltxregstexpenses` decimal(20,2) NOT NULL default '0.00',
  `localtotaltxn43gstclaimable` decimal(20,2) NOT NULL default '0.00',
  `descr` varchar(200) NOT NULL default '',
  `refno` varchar(20) NOT NULL default '',
  `note` mediumtext,
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `debitacccode` varchar(20) NOT NULL default '',
  `creditacccode` varchar(20) NOT NULL default '',
  `journalkey` int(11) unsigned default NULL,
  PRIMARY KEY  (`partialexemptionadjustmentkey`),
  UNIQUE KEY `ipeacode` (`partialexemptionadjustmentcode`),
  KEY `ipeadate` (`partialexemptionadjustmentdate`),
  KEY `ipeataxperiodstartdate` (`taxperiodstartdate`),
  KEY `ipeataxperiodenddate` (`taxperiodenddate`),
  KEY `ipeacancelled` (`cancelled`),
  KEY `ipeadebitacccode` (`debitacccode`),
  KEY `peacreditacccode` (`creditacccode`),
  KEY `ipeajournalkey` (`journalkey`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `partialexemptionadjustmenttxre` (
  `partialexemptionadjustmentkey` int(11) unsigned NOT NULL auto_increment,
  `partialexemptionadjustmentcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `gsttransdate` date default NULL,
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `docid` int(11) unsigned NOT NULL default '0',
  `gstdescr` varchar(200) NOT NULL default '',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('Supply','Purchase') NOT NULL default 'Supply',
  `taxpercentage` decimal(20,2) default NULL,
  `taxableacccode` varchar(20) NOT NULL default '',
  `taxableaccname` varchar(200) NOT NULL default '',
  `taxacccode` varchar(20) NOT NULL default '',
  `taxaccname` varchar(200) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `regno` varchar(30) NOT NULL default '',
  `msiccode` varchar(20) NOT NULL default '',
  `gst03_item16` enum('Y','N') NOT NULL default 'N',
  `irr` decimal(20,2) NOT NULL default '0.00',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `gstclaimed` decimal(20,2) NOT NULL default '0.00',
  `gstexpenses` decimal(20,2) NOT NULL default '0.00',
  `localtaxableamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxamount` decimal(20,2) NOT NULL default '0.00',
  `localgstclaimed` decimal(20,2) NOT NULL default '0.00',
  `localgstexpenses` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  USING BTREE (`partialexemptionadjustmentkey`,`pos`,`doccode`),
  UNIQUE KEY `ipeatxrecodepos` (`partialexemptionadjustmentcode`,`pos`),
  KEY `ipeatxredoctypekeypos` (`doctype`,`dockey`,`docpos`),
  KEY `ipeatxredoctypecodepos` (`doctype`,`doccode`,`docpos`),
  KEY `ipeatxredoctypekeyid` (`doctype`,`dockey`,`docid`),
  KEY `ipeatxredoctypecodeid` (`doctype`,`doccode`,`docid`),
  KEY `ipeatxrecurcode` (`curcode`),
  KEY `ipeatxredepartmentcode` (`departmentcode`),
  KEY `ipeatxrejobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `partialexemptionadjustmenttxn43` (
  `partialexemptionadjustmentkey` int(11) unsigned NOT NULL auto_increment,
  `partialexemptionadjustmentcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `gsttransdate` date default NULL,
  `doctype` varchar(5) NOT NULL default '',
  `dockey` int(11) unsigned NOT NULL default '0',
  `doccode` varchar(20) NOT NULL default '',
  `docpos` int(11) unsigned NOT NULL default '0',
  `docid` int(11) unsigned NOT NULL default '0',
  `gstdescr` varchar(200) NOT NULL default '',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('Supply','Purchase') NOT NULL default 'Supply',
  `taxpercentage` decimal(20,2) default NULL,
  `taxableacccode` varchar(20) NOT NULL default '',
  `taxableaccname` varchar(200) NOT NULL default '',
  `taxacccode` varchar(20) NOT NULL default '',
  `taxaccname` varchar(200) NOT NULL default '',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `regno` varchar(30) NOT NULL default '',
  `msiccode` varchar(20) NOT NULL default '',
  `gst03_item16` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxableamount` decimal(20,2) NOT NULL default '0.00',
  `localtaxamount` decimal(20,2) NOT NULL default '0.00',
  PRIMARY KEY  USING BTREE (`partialexemptionadjustmentkey`,`pos`,`doccode`),
  UNIQUE KEY `ipeatxn43codepos` (`partialexemptionadjustmentcode`,`pos`),
  KEY `ipeatxn43doctypekeypos` (`doctype`,`dockey`,`docpos`),
  KEY `ipeatxn43doctypecodepos` (`doctype`,`doccode`,`docpos`),
  KEY `ipeatxn43doctypekeyid` (`doctype`,`dockey`,`docid`),
  KEY `ipeatxn43doctypecodeid` (`doctype`,`doccode`,`docid`),
  KEY `ipeatxn43curcode` (`curcode`),
  KEY `ipeatxn43departmentcode` (`departmentcode`),
  KEY `ipeatxn43jobcode` (`jobcode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `payment` (
  `paymentkey` int(11) unsigned NOT NULL auto_increment,
  `paymentcode` varchar(20) NOT NULL default '',
  `cashbooktypecode` varchar(20) NOT NULL default '',
  `paymentdate` date default NULL,
  `paymentacccode` varchar(20) NOT NULL default '',
  `acccode` varchar(20) NOT NULL default '',
  `payto` varchar(50) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `chequeno` varchar(20) NOT NULL default '',
  `multi` enum('Y','N') NOT NULL default 'N',
  `amount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `areacode` varchar(20) NOT NULL default '',
  `remark1` varchar(200) NOT NULL,
  `remark2` varchar(200) NOT NULL,
  `remark3` varchar(200) NOT NULL,
  `remark4` varchar(200) NOT NULL,
  `remark5` varchar(200) NOT NULL,
  `remark6` varchar(200) NOT NULL,
  `remark7` varchar(200) NOT NULL,
  `remark8` varchar(200) NOT NULL,
  `note` mediumtext,
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `cancelled` enum('Y','N') NOT NULL default 'N',
  `cancelledremark` varchar(200) NOT NULL,
  `done` enum('Y','N') NOT NULL default 'N',
  `curcode` varchar(20) NOT NULL,
  `currate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `localamount` decimal(20,2) NOT NULL default '0.00',
  `bankchargeacccode` varchar(20) NOT NULL default '',
  `bcurcode` varchar(20) NOT NULL default '',
  `bcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `bamount` decimal(20,2) NOT NULL default '0.00',
  `bankcharges` decimal(20,2) NOT NULL default '0.00',
  `blocalcurrate` decimal(20,8) unsigned NOT NULL default '1.00000000',
  `blocalamount` decimal(20,2) NOT NULL default '0.00',
  `localbankcharges` decimal(20,2) NOT NULL default '0.00',
  `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `totaltaxableamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxamount` decimal(20,2) NOT NULL default '0.00',
  `totaltaxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `totalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `totallocalamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `fromdoctype` varchar(5) NOT NULL default '',
  `fromdockey` int(11) unsigned default '0',
  `fromdoccode` varchar(20) NOT NULL default '',
  `baddebtgsttypecode` varchar(20) NOT NULL default '',
  `creditorgstbaddebtrecovercode1st` varchar(20) NOT NULL default '',
  `journalcreditorgstbaddebtrecovercode1st` varchar(20) NOT NULL default '',
  `generatebankpayment` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`paymentkey`),
  UNIQUE KEY `ipcode` (`paymentcode`),
  KEY `ipdate` (`paymentdate`),
  KEY `ippaymentacccode` (`paymentacccode`),
  KEY `ipacccode` (`acccode`),
  KEY `ipchequeno` (`chequeno`),
  KEY `ipmulti` (`multi`),
  KEY `ipdepartmentcode` (`departmentcode`),
  KEY `ipstaffcode` (`staffcode`),
  KEY `ipareacode` (`areacode`),
  KEY `ipcancelled` (`cancelled`),
  KEY `ipcurcode` (`curcode`),
  KEY `ipcashbooktypecode` (`cashbooktypecode`),
  KEY `ipjobcode` (`jobcode`),
  KEY `ipbankchargeacccode` (`bankchargeacccode`),
  KEY `ipbcurcode` (`bcurcode`),
  KEY `ipfromdoctype` (`fromdoctype`),
  KEY `ipfromdockey` (`fromdockey`),
  KEY `ipfromdoccode` (`fromdoccode`),
  KEY `ipbaddebtgsttypecode` (`baddebtgsttypecode`),
  KEY `ipcreditorgstbaddebtrecovercode1st` (`creditorgstbaddebtrecovercode1st`),
  KEY `ipjournalcreditorgstbaddebtrecovercode1st` (`journalcreditorgstbaddebtrecovercode1st`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE TABLE `paymentmulti` (
  `paymentkey` int(11) unsigned NOT NULL default '0',
  `paymentcode` varchar(20) NOT NULL default '',
  `pos` int(11) unsigned NOT NULL default '0',
  `acccode` varchar(20) NOT NULL default '',
  `descr` varchar(200) NOT NULL default '',
  `descr2` varchar(200) NOT NULL default '',
  `subamount` decimal(20,2) NOT NULL default '0.00',
  `staffcode` varchar(20) NOT NULL default '',
  `departmentcode` varchar(20) NOT NULL default '',
  `jobcode` varchar(20) NOT NULL default '',
  `localsubamount` decimal(20,2) NOT NULL default '0.00',
  `refno` varchar(20) NOT NULL default '',
  `refno2` varchar(20) NOT NULL default '',
  `splitprint` enum('Y','N') NOT NULL default 'N',
  `amountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `localamountbeforetax` decimal(20,2) NOT NULL default '0.00',
  `gsttypecode` varchar(20) NOT NULL default '',
  `supplypurchase` enum('None','Supply','Purchase') NOT NULL default 'None',
  `taxpercentage` decimal(20,2) default NULL,
  `taxacccode` varchar(20) NOT NULL default '',
  `taxsubtract` enum('Y','N') NOT NULL default 'N',
  `taxableamount` decimal(20,2) NOT NULL default '0.00',
  `taxablelocalamount` decimal(20,2) NOT NULL default '0.00',
  `amountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `localamountaftertaxsubtract` decimal(20,2) NOT NULL default '0.00',
  `taxamount` decimal(20,2) NOT NULL default '0.00',
  `taxlocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxadjustment` decimal(20,2) NOT NULL default '0.00',
  `taxlocaladjustment` decimal(20,2) NOT NULL default '0.00',
  `taxtotalamount` decimal(20,2) NOT NULL default '0.00',
  `taxtotallocalamount` decimal(20,2) NOT NULL default '0.00',
  `taxrefno` varchar(50) NOT NULL default '',
  `generatebankpayment` enum('Y','N') NOT NULL default 'N',
  PRIMARY KEY  (`paymentkey`,`pos`),
  KEY `ipmacccode` (`acccode`),
  KEY `ipmcode` (`paymentcode`),
  KEY `ipmdepartmentcode` (`departmentcode`),
  KEY `ipmjobcode` (`jobcode`),
  KEY `ipmstaffcode` (`staffcode`),
  KEY `ipmgsttypecode` (`gsttypecode`),
  KEY `ipmtaxacccode` (`taxacccode`)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

