-------------------------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS `m_ImportHeader` ( `CompanyID` varchar(15) NOT NULL COMMENT '会社ID Comany ID P Key', `FK1` int NOT NULL COMMENT 'Target File Kind: BankStatement (1:ON,0:OFF)', `FK2` int NOT NULL COMMENT 'Target File Kind: Reserve', `FK3` int NOT NULL COMMENT 'Target File Kind: Reserve', `FK4` int NOT NULL COMMENT 'Target File Kind: Reserve', `FK5` int NOT NULL COMMENT 'Target File Kind: Reserve', `TN1` varchar(150) COMMENT 'TempTable Name: BankStatement', `TN2` varchar(150) COMMENT 'TempTable Name: Reserve', `TN3` varchar(150) COMMENT 'TempTable Name: Reserve', `TN4` varchar(150) COMMENT 'TempTable Name: Reserve', `TN5` varchar(150) COMMENT 'TempTable Name: Reserve', `TD1` varchar(150) COMMENT 'TempTable Kind: Bank (for BankStatement)', `TD2` varchar(150) COMMENT 'TempTable Kind: Reserve', `TD3` varchar(150) COMMENT 'TempTable Kind: Reserve', `TD4` varchar(150) COMMENT 'TempTable Kind: Reserve', `TD5` varchar(150) COMMENT 'TempTable Kind: Reserve', `EntDate` datetime NOT NULL COMMENT '作成日', `EntUser` varchar(24) NOT NULL COMMENT '作成者', `UpdDate` datetime NOT NULL COMMENT '最終更新日', `UpdUser` varchar(24) NOT NULL COMMENT '最終更新者', `DelDate` datetime DEFAULT NULL COMMENT '削除日', `DelUser` varchar(24) DEFAULT NULL COMMENT '削除者', `Status` bit(1) NOT NULL COMMENT '状態', PRIMARY KEY (`CompanyID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf16; insert into m_ImportHeader VALUES ('OPE',1,0,0,0,0,'t_tmpImportBankState','','','','','Bank','','','','','2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportHeader VALUES ('OPH',1,0,0,0,0,'t_tmpImportBankState','','','','','Bank','','','','','2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); CREATE TABLE IF NOT EXISTS `m_ImportData` ( `CompanyID` varchar(15) NOT NULL COMMENT '会社ID Comany ID P Key', `FKNo` int NOT NULL COMMENT 'Target File Kind P-Key', `DocKind` int NOT NULL COMMENT 'Target Document Kind P-Key', `SeqNo` Int NOT NULL COMMENT 'Seq no in FKNo P-Key', `DataName` varchar(25) COMMENT 'Data Name for Program', `DataField` varchar(256) COMMENT 'Data Field Name for File', `RelateField` varchar(25) COMMENT 'Relate Data Field for TempTable', `DataType` int NOT NULL COMMENT 'Data Type (1;int/2;char/3;dec/4;DateTime)', `EntDate` datetime NOT NULL COMMENT '作成日', `EntUser` varchar(24) NOT NULL COMMENT '作成者', `UpdDate` datetime NOT NULL COMMENT '最終更新日', `UpdUser` varchar(24) NOT NULL COMMENT '最終更新者', `DelDate` datetime DEFAULT NULL COMMENT '削除日', `DelUser` varchar(24) DEFAULT NULL COMMENT '削除者', `Status` bit(1) NOT NULL COMMENT '状態', PRIMARY KEY (`CompanyID`,`FKNo`,`DocKind`,`SeqNo`) ) ENGINE=InnoDB DEFAULT CHARSET=utf16; insert into m_ImportData VALUES ('OPE',1,1,1,'DDate','Date','DataDate',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,2,'DTime','Time/Eff Date','DataTime',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,3,'Desc','Description','Description',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,4,'Cheque','ChequeNo','ChequeNo',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,5,'Withdrawal','Withdrawal','Withdrawal',3,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,6,'Deposit','Deposit','Deposit',3,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,7,'Balance','Outstanding Balance','Balance',3,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,8,'Channel','Channel','Channel',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,9,'Currency','Currency','Currency',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,10,'AccCode','Account Code','AccCode',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,11,'Attn','Attn','Attn',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,1,12,'button','','',1,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,1,'DDate','Date2','DataDate',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,2,'DTime','Time/Eff Date2','DataTime',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,3,'Desc','Description2','Description',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,4,'Cheque','ChequeNo2','ChequeNo',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,5,'Withdrawal','Withdrawal2','Withdrawal',3,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,6,'Deposit','Deposit2','Deposit',3,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,7,'Balance','Outstanding Balance2','Balance',3,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,8,'Channel','Channel2','Channel',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,9,'Currency','Currency2','Currency',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,10,'AccCode','Account Code2','AccCode',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,11,'Attn','Attn2','Attn',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,2,12,'button','','',1,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,1,'DDate','Date3','DataDate',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,2,'DTime','Time/Eff Date3','DataTime',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,3,'Desc','Description3','Description',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,4,'Cheque','ChequeNo3','ChequeNo',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,5,'Withdrawal','Withdrawal3','Withdrawal',3,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,6,'Deposit','Deposit3','Deposit',3,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,7,'Balance','Outstanding Balance3','Balance',3,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,8,'Channel','Channel3','Channel',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,9,'Currency','Currency3','Currency',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,10,'AccCode','Account Code3','AccCode',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,11,'Attn','Attn3','Attn',2,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportData VALUES ('OPE',1,3,12,'button','','',1,'2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); /* ------------------------------------------------------------------------- DELETE Table----- Bank/Account関連変更で本Tableは不使用--汎用化では使用予定見直し中 ------------------Change Start---------------- CREATE TABLE IF NOT EXISTS `m_ImportDoc` ( `CompanyID` varchar(15) NOT NULL COMMENT '会社ID Comany ID P Key', `FKNo` int NOT NULL COMMENT 'Target File Kind P-Key', `DocKind` int NOT NULL COMMENT 'Target Document Kind P-Key', `Docname` varchar(50) COMMENT 'Document name', `EntDate` datetime NOT NULL COMMENT '作成日', `EntUser` varchar(24) NOT NULL COMMENT '作成者', `UpdDate` datetime NOT NULL COMMENT '最終更新日', `UpdUser` varchar(24) NOT NULL COMMENT '最終更新者', `DelDate` datetime DEFAULT NULL COMMENT '削除日', `DelUser` varchar(24) DEFAULT NULL COMMENT '削除者', `Status` bit(1) NOT NULL COMMENT '状態', PRIMARY KEY (`CompanyID`,`FKNo`,`DocKind`) ) ENGINE=InnoDB DEFAULT CHARSET=utf16; insert into m_ImportDoc VALUES ('OPE',1,1,'Bangkok Bank','2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportDoc VALUES ('OPE',1,2,'Kashikorn Bank','2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportDoc VALUES ('OPE',1,3,'Siam Commercial Bank','2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); ---------------Change End---------- ----------------------------------------------------------------*/ CREATE TABLE IF NOT EXISTS `m_ImportJournal` ( `CompanyID` varchar(15) NOT NULL COMMENT '会社ID Comany ID P Key', `FKNo` int NOT NULL COMMENT 'Target Dec File Kind P-Key', `BankID` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '銀行ID PKey', `Description` varchar(150) NOT NULL COMMENT 'Statement Description Text P-Key', `AccCode` varchar(15) COMMENT 'Relate Account Code', `Attn` varchar(50) COMMENT 'Relate Business associate', `EntDate` datetime NOT NULL COMMENT '作成日', `EntUser` varchar(24) NOT NULL COMMENT '作成者', `UpdDate` datetime NOT NULL COMMENT '最終更新日', `UpdUser` varchar(24) NOT NULL COMMENT '最終更新者', `DelDate` datetime DEFAULT NULL COMMENT '削除日', `DelUser` varchar(24) DEFAULT NULL COMMENT '削除者', `Status` bit(1) NOT NULL COMMENT '状態', PRIMARY KEY (`CompanyID`,`FKNo`,`BankID`,`Description`) ) ENGINE=InnoDB DEFAULT CHARSET=utf16; insert into m_ImportJournal VALUES ('OPE',1,'11','Automatic Deposit SMART BAY', '23000','','2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); insert into m_ImportJournal VALUES ('OPE',1,'11','Cheque Deposit BAY', '53000','','2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); /*------------------------------------------------------------------------------ Primary Key add (BankAccountID) for Bank/Account change and bank field change bank(int)--->bankID(varchar24) ----------------------------------*/ CREATE TABLE IF NOT EXISTS `t_tmpImportBankState` ( `CompanyID` varchar(15) NOT NULL COMMENT '会社ID Comany ID P Key', `BankID` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '銀行ID PKey', `BankAccountNo` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '口座番号（タイでは一般的に10桁）', `DataDate` varchar(8) NOT NULL COMMENT 'ImportData: Date (YYYYMMDD) P-Key', `DataTime` varchar(6) NOT NULL COMMENT 'ImportData: Time (HHMMSS) P-Key', `Description` varchar(150) COMMENT 'ImportData: Description Text', `ChequeNo` varchar(15) COMMENT 'ImportData: Cheque No Text', `Withdrawal` decimal(12,3) NOT NULL COMMENT 'ImportData: withdrwal price', `Deposit` decimal(12,3) NOT NULL COMMENT 'ImportData: Deposit price', `Balance` decimal(12,3) NOT NULL COMMENT 'ImportData: Balance price', `Channel` varchar(30) COMMENT 'ImportData: Channel Text', `Currency` varchar(5) COMMENT 'ImportData: Currency Text', `AccCode` varchar(15) COMMENT 'Relate Account Code', `Attn` varchar(50) COMMENT 'Relate Business associate', `EntDate` datetime NOT NULL COMMENT '作成日', `EntUser` varchar(24) NOT NULL COMMENT '作成者', `UpdDate` datetime NOT NULL COMMENT '最終更新日', `UpdUser` varchar(24) NOT NULL COMMENT '最終更新者', `DelDate` datetime DEFAULT NULL COMMENT '削除日', `DelUser` varchar(24) DEFAULT NULL COMMENT '削除者', `Status` bit(1) NOT NULL COMMENT '状態', PRIMARY KEY (`CompanyID`,`BankID`,`BankAccountNo`,`DataDate`,`DataTime`) ) ENGINE=InnoDB DEFAULT CHARSET=utf16; insert into t_tmpImportBankState VALUES ('OPE','11','1111111','20220303','000000','Automatic Deposit SMART BAY','','1000','0','20000','','THB', '23000','','2022-03-03 00:00:00','ope','2022-03-03 00:00:00','ope',NULL,NULL,b'1'); /*---------------------add table sample-------------------------------------------*/ /* CREATE TABLE `u_bank` ( `BankNo` INT(12) NOT NULL AUTO_INCREMENT, `BankID` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '銀行ID', `LanguageID` VARCHAR(2) NOT NULL COMMENT '言語ID', `BankName` VARCHAR(120) NOT NULL COMMENT '銀行名称', `DocKind` INT(11) NOT NULL COMMENT 'Target Document Kind P-Key　(for Import)', `EntDate` DATETIME NOT NULL COMMENT '作成日', `EntUser` VARCHAR(24) NOT NULL COMMENT '作成者', `UpdDate` DATETIME NOT NULL COMMENT '最終更新日', `UpdUser` VARCHAR(24) NOT NULL COMMENT '最終更新者', `DelDate` DATETIME NULL DEFAULT NULL COMMENT '削除日', `DelUser` VARCHAR(24) NULL DEFAULT NULL COMMENT '削除者', `Status` BIT(1) NOT NULL COMMENT '状態', PRIMARY KEY (`BankNo`) USING BTREE ) ENGINE=InnoDB ; CREATE TABLE `u_bankaccount` ( `BankAccountCode` INT(12) NOT NULL AUTO_INCREMENT, `BankID` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '銀行ID', `BankAccountID` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '口座ID（ユーザーが指定）', `LanguageID` VARCHAR(2) NOT NULL COMMENT '言語ID', `BankAccountName` VARCHAR(120) NOT NULL COMMENT '銀行口座名称', `BankAccountNo` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '口座番号（タイでは一般的に10桁）', `AccountsTitleID` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '勘定科目コード（ユーザー）', `EntDate` DATETIME NOT NULL COMMENT '作成日', `EntUser` VARCHAR(24) NOT NULL COMMENT '作成者', `UpdDate` DATETIME NOT NULL COMMENT '最終更新日', `UpdUser` VARCHAR(24) NOT NULL COMMENT '最終更新者', `DelDate` DATETIME NULL DEFAULT NULL COMMENT '削除日', `DelUser` VARCHAR(24) NULL DEFAULT NULL COMMENT '削除者', `Status` BIT(1) NOT NULL COMMENT '状態', PRIMARY KEY (`BankAccountCode`) USING BTREE ) ENGINE=InnoDB ; */