prompt PL/SQL Developer import file
prompt Created on 28 02, 2013 by Vu Van Cuong
set feedback off
set define off
prompt Dropping TBLLETTERMSGX_GTW ...
drop table TBLLETTERMSGX_GTW cascade constraints;
prompt Dropping TBLLETTERMSG_GTW...
drop table TBLLETTERMSG_GTW cascade constraints;
prompt Creating TBLLETTERMSGX_GTW...

create table TBLLETTERMSGX_GTW
(
  CHECK_CODE              VARCHAR2(2 CHAR),
  CREATE_FILE_RESULT_FLAG VARCHAR2(1 CHAR),
  FILE_NAME               VARCHAR2(25 CHAR),
  SD_TIME                 VARCHAR2(14 CHAR),
  RESPONSE_CODE           VARCHAR2(4 CHAR),
  SERIAL_NO               VARCHAR2(8 CHAR),
  O_CI_CODE               VARCHAR2(12 CHAR) not null,
  R_CI_CODE               VARCHAR2(12 CHAR) not null,
  TRX_DATE                VARCHAR2(8 CHAR) not null,
  LETTER_CONTENT          VARCHAR2(210 CHAR),
  REPLY_CONTENT           VARCHAR2(210 CHAR) not null,
  RELATION_NO_ORG         VARCHAR2(40 CHAR),
  O_CI_CODE_ORG           VARCHAR2(12 CHAR),
  R_CI_CODE_ORG           VARCHAR2(12 CHAR),
  TRX_DATE_ORG            VARCHAR2(8 CHAR),
  SERIAL_NO_ORG           VARCHAR2(8 CHAR),
  CURRENCY_ORG            VARCHAR2(3 CHAR),
  AMOUNT_ORG              VARCHAR2(22 CHAR),
  O_CI_CODE_LET           VARCHAR2(12 CHAR) not null,
  R_CI_CODE_LET           VARCHAR2(12 CHAR) not null,
  TRX_DATE_LET            VARCHAR2(8 CHAR) not null,
  SERIAL_NO_LET           VARCHAR2(8 CHAR) not null,
  EX_E_SIGN               VARCHAR2(400 CHAR),
  MAC                     VARCHAR2(28 CHAR),
  APPR_ID                 VARCHAR2(16 CHAR),
  CREATE_TIME             VARCHAR2(14 CHAR),
  E_SIGN                  VARCHAR2(400 CHAR),
  OPTIONCODE              VARCHAR2(30 CHAR),
  CONTENTFROMFILE         VARCHAR2(4000 CHAR),
  ERR_MSG                 VARCHAR2(200 CHAR),
  LINEPOSITION            NUMBER(20),
  FILEPROCESSINGTIME      VARCHAR2(14 CHAR),
  TRX_TYPE_LET            VARCHAR2(6 CHAR),
  APPR_TIME               VARCHAR2(14 CHAR),
  TRX_TYPE                VARCHAR2(6 CHAR)
);
alter table TBLLETTERMSGX_GTW add constraint TBLLETTERMSGX_GTW_PK primary key (O_CI_CODE, R_CI_CODE, TRX_DATE, O_CI_CODE_LET, R_CI_CODE_LET, TRX_DATE_LET, SERIAL_NO_LET);

prompt Creating TBLLETTERMSG_GTW...
create table TBLLETTERMSG_GTW
(
  CHECK_CODE              VARCHAR2(2 CHAR),
  CREATE_FILE_RESULT_FLAG VARCHAR2(1 CHAR),
  FILE_NAME               VARCHAR2(25 CHAR),
  SD_TIME                 VARCHAR2(14 CHAR),
  RESPONSE_CODE           VARCHAR2(4 CHAR),
  SERIAL_NO               VARCHAR2(8 CHAR),
  O_CI_CODE               VARCHAR2(12 CHAR) not null,
  R_CI_CODE               VARCHAR2(12 CHAR) not null,
  TRX_DATE                VARCHAR2(8 CHAR) not null,
  LETTER_CONTENT          VARCHAR2(210 CHAR) not null,
  RELATION_NO_ORG         VARCHAR2(40 CHAR),
  O_CI_CODE_ORG           VARCHAR2(12 CHAR) not null,
  R_CI_CODE_ORG           VARCHAR2(12 CHAR) not null,
  TRX_DATE_ORG            VARCHAR2(8 CHAR) not null,
  SERIAL_NO_ORG           VARCHAR2(8 CHAR) not null,
  CURRENCY_ORG            VARCHAR2(3 CHAR),
  AMOUNT_ORG              VARCHAR2(22 CHAR),
  EX_E_SIGN               VARCHAR2(400 CHAR),
  MAC                     VARCHAR2(28 CHAR),
  APPR_ID                 VARCHAR2(16 CHAR),
  CREATE_TIME             VARCHAR2(14 CHAR) not null,
  E_SIGN                  VARCHAR2(400 CHAR),
  OPTIONCODE              VARCHAR2(30 CHAR),
  CONTENTFROMFILE         VARCHAR2(4000 CHAR),
  ERR_MSG                 VARCHAR2(200 CHAR),
  LINEPOSITION            NUMBER(20),
  FILEPROCESSINGTIME      VARCHAR2(14 CHAR),
  TRX_TYPE_ORG            VARCHAR2(6 CHAR) not null,
  APPR_TIME               VARCHAR2(14 CHAR),
  O_INDIRECT_ORG          VARCHAR2(12 CHAR),
  R_INDIRECT_ORG          VARCHAR2(12 CHAR),
  TRX_TYPE                VARCHAR2(6 CHAR)
);
alter table TBLLETTERMSG_GTW add constraint TBLLETTERMSG_GTW_PK primary key (O_CI_CODE, R_CI_CODE, TRX_DATE, O_CI_CODE_ORG, R_CI_CODE_ORG, TRX_DATE_ORG, SERIAL_NO_ORG, CREATE_TIME);

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', '');
commit;
set feedback on
set define on
prompt Done.
