delete status_list WHERE status_type='12' AND status_code='03';
INSERT INTO status_list(status_type,status_code,desc_vn,desc_en) VALUES('12','03','IBPS1','IBPS1');
delete status_list WHERE status_type='03' AND status_code='03';
insert into STATUS_LIST(status_type,status_code,desc_vn,desc_en) values('03','03',N'Giao dịch đi,thành công','Outgoing Transaction,Success');
delete status_list WHERE status_type='03' AND status_code='04';
insert into STATUS_LIST(status_type,status_code,desc_vn,desc_en) values('03','04',N'Giao dịch đi,không thành công','Outgoing Transaction,Unsuccess');

delete from TBLLISTRESPONSECODE where RESPONSE_CODE in ('4700','7102','7603','7604','7605','7518','7502','7210','7202','8603','8604','8605','8518','8502','8210','8202','1335','1423');
insert into TBLLISTRESPONSECODE values('4700','Create successful list of requested file',N'Tạo thành công danh sách file được yêu cầu',null,null);
insert into TBLLISTRESPONSECODE values('7102','Canceled at end of day',N'Ðã bị hủy cuối ngày',null,null);
insert into TBLLISTRESPONSECODE values('7603','Canceled at end of day',N'Đã bị hủy cuối ngày',null,null);
insert into TBLLISTRESPONSECODE values('7604','Canceled at end of day',N'Đã bị hủy cuối ngày',null,null);
insert into TBLLISTRESPONSECODE values('7605','Canceled at end of day',N'Đã bị hủy cuối ngày',null,null);
insert into TBLLISTRESPONSECODE values('7518','Canceled at end of day',N'Đã bị hủy cuối ngày',null,null);
insert into TBLLISTRESPONSECODE values('7502','Canceled at end of day',N'Đã bị hủy cuối ngày',null,null);
insert into TBLLISTRESPONSECODE values('7210','Canceled at end of day',N'Đã bị hủy cuối ngày',null,null);
insert into TBLLISTRESPONSECODE values('7202','Canceled at end of day',N'Đã bị hủy cuối ngày',null,null);
insert into TBLLISTRESPONSECODE values('8603','Payment was canceled',N'Lệnh chuyển tiền đã bị hủy',null,null);
insert into TBLLISTRESPONSECODE values('8604','Payment was canceled',N'Lệnh chuyển tiền đã bị hủy',null,null);
insert into TBLLISTRESPONSECODE values('8605','Payment was canceled',N'Lệnh chuyển tiền đã bị hủy',null,null);
insert into TBLLISTRESPONSECODE values('8518','Payment was canceled',N'Lệnh chuyển tiền đã bị hủy',null,null);
insert into TBLLISTRESPONSECODE values('8502','Payment was canceled',N'Lệnh chuyển tiền đã bị hủy',null,null);
insert into TBLLISTRESPONSECODE values('8210','Payment was canceled',N'Lệnh chuyển tiền đã bị hủy',null,null);
insert into TBLLISTRESPONSECODE values('8202','Payment was canceled',N'Lệnh chuyển tiền đã bị hủy',null,null);
insert into TBLLISTRESPONSECODE values('1335','Invalid bank_code',N'Mã ngân hàng không hợp lệ',null,null);
insert into TBLLISTRESPONSECODE values ('1423','Could not find original letter message',N'Không tìm thấy lệnh tra soát gốc',null,null);

delete from STATUS_LIST where STATUS_TYPE = '13';
insert into STATUS_LIST (STATUS_TYPE,STATUS_CODE,DESC_VN,DESC_EN)values ('13','00',N'Chờ xử lý','Processing');
insert into STATUS_LIST (STATUS_TYPE,STATUS_CODE,DESC_VN,DESC_EN)values ('13','01',N'Có lỗi trong quá trình xử lý','Error');
insert into STATUS_LIST (STATUS_TYPE,STATUS_CODE,DESC_VN,DESC_EN)values ('13','02',N'Đã chuyển CITAD','imported Success');
insert into STATUS_LIST (STATUS_TYPE,STATUS_CODE,DESC_VN,DESC_EN)values ('13','03',N'Xử lý thành công','Success');
insert into STATUS_LIST (STATUS_TYPE,STATUS_CODE,DESC_VN,DESC_EN)values ('13','04',N'Xử lý không thành công','Unsuccess');
insert into STATUS_LIST (STATUS_TYPE,STATUS_CODE,DESC_VN,DESC_EN)values ('13','05',N'Từ CI sang GW','Incomming');

delete from TBLCI_PARA_GTW where PARA_NAME in ('PRIV_KEY','PATH_LETTER_INCOMING','PATH_LETTER_OUTGOING','PATH_LETTER_OUTGOING_BACKUP','PATH_LETTER_RESPONSE');
insert into TBLCI_PARA_GTW (PARA_NAME,PARA_VALUE) values ('PATH_LETTER_INCOMING','C:\CI_GTW\LETTER\EXPORT');
insert into TBLCI_PARA_GTW (PARA_NAME,PARA_VALUE) values ('PATH_LETTER_OUTGOING',' C:\CI_GTW\LETTER\IMPORT');
insert into TBLCI_PARA_GTW (PARA_NAME,PARA_VALUE) values ('PATH_LETTER_OUTGOING_BACKUP','C:\CI_GTW\LETTER\BACKUP');
insert into TBLCI_PARA_GTW (PARA_NAME,PARA_VALUE) values ('PATH_LETTER_RESPONSE','C:\CI_GTW\LETTER\RESPONSE');
insert into TBLCI_PARA_GTW (PARA_NAME,PARA_VALUE) values ('PRIV_KEY','');

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBLLETTERMSG_GTW]') and OBJECTPROPERTY(id,N'IsUserTable') = 1) drop table [dbo].[TBLLETTERMSG_GTW];
create table TBLLETTERMSG_GTW(CHECK_CODE NVARCHAR(2),CREATE_FILE_RESULT_FLAG NVARCHAR(1),FILE_NAME NVARCHAR(25),SD_TIME NVARCHAR(14),RESPONSE_CODE NVARCHAR(4),SERIAL_NO NVARCHAR(8),O_CI_CODE NVARCHAR(12) not null,R_CI_CODE NVARCHAR(12) not null,TRX_DATE NVARCHAR(8) not null,LETTER_CONTENT NVARCHAR(210) not null,RELATION_NO_ORG NVARCHAR(40),O_CI_CODE_ORG NVARCHAR(12) not null,R_CI_CODE_ORG NVARCHAR(12) not null,TRX_DATE_ORG NVARCHAR(8) not null,SERIAL_NO_ORG NVARCHAR(8) not null,CURRENCY_ORG NVARCHAR(3),AMOUNT_ORG NVARCHAR(22),EX_E_SIGN NVARCHAR(400),MAC NVARCHAR(28),APPR_ID NVARCHAR(16),CREATE_TIME NVARCHAR(14) not null,E_SIGN NVARCHAR(400),OPTIONCODE NVARCHAR(30),CONTENTFROMFILE NVARCHAR(4000),ERR_MSG NVARCHAR(200),LINEPOSITION decimal(20),FILEPROCESSINGTIME VARCHAR(14),TRX_TYPE_ORG NVARCHAR(6) not null,APPR_TIME NVARCHAR(14),O_INDIRECT_ORG NVARCHAR(12),R_INDIRECT_ORG NVARCHAR(12),TRX_TYPE NVARCHAR(6));
ALTER TABLE [dbo].[TBLLETTERMSG_GTW] ADD CONSTRAINT [PK_TBLLETTERMSG_GTW] PRIMARY KEY CLUSTERED ([O_CI_CODE],[R_CI_CODE],[TRX_DATE],[CREATE_TIME],[O_CI_CODE_ORG],[R_CI_CODE_ORG],[TRX_DATE_ORG],[SERIAL_NO_ORG]) ON [PRIMARY]; 

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TBLLETTERMSGX_GTW]') and OBJECTPROPERTY(id,N'IsUserTable') = 1) drop table [dbo].[TBLLETTERMSGX_GTW];
create table TBLLETTERMSGX_GTW(CHECK_CODE NVARCHAR(2),CREATE_FILE_RESULT_FLAG NVARCHAR(1),FILE_NAME NVARCHAR(25),SD_TIME NVARCHAR(14),RESPONSE_CODE NVARCHAR(4),SERIAL_NO NVARCHAR(8),O_CI_CODE NVARCHAR(12) not null,R_CI_CODE NVARCHAR(12) not null, TRX_DATE NVARCHAR(8) not null,LETTER_CONTENT NVARCHAR(210),REPLY_CONTENT NVARCHAR(210) not null,RELATION_NO_ORG NVARCHAR(40),O_CI_CODE_ORG NVARCHAR(12),R_CI_CODE_ORG NVARCHAR(12),TRX_DATE_ORG NVARCHAR(8),SERIAL_NO_ORG NVARCHAR(8),CURRENCY_ORG NVARCHAR(3),AMOUNT_ORG  NVARCHAR(22),O_CI_CODE_LET NVARCHAR(12) not null,R_CI_CODE_LET NVARCHAR(12) not null,TRX_DATE_LET NVARCHAR(8) not null,SERIAL_NO_LET NVARCHAR(8) not null,EX_E_SIGN NVARCHAR(400),MAC NVARCHAR(28),APPR_ID NVARCHAR(16),CREATE_TIME NVARCHAR(14) not null,E_SIGN NVARCHAR(400),OPTIONCODE NVARCHAR(30),CONTENTFROMFILE NVARCHAR(4000),ERR_MSG NVARCHAR(200),LINEPOSITION decimal(20),FILEPROCESSINGTIME VARCHAR(14),TRX_TYPE_LET NVARCHAR(6) not null,APPR_TIME NVARCHAR(14),TRX_TYPE NVARCHAR(6));
ALTER TABLE [dbo].[TBLLETTERMSGX_GTW] ADD CONSTRAINT [PK_TBLLETTERMSGX_GTW] PRIMARY KEY CLUSTERED ([O_CI_CODE],[R_CI_CODE],[TRX_DATE],[O_CI_CODE_LET],[R_CI_CODE_LET],[TRX_DATE_LET],[SERIAL_NO_LET]) ON [PRIMARY];




