DROP TABLE TRX CASCADE CONSTRAINTS ; 

create table TRX
(
 TRX_ID VARCHAR2(50) NOT NULL,
 TRX_DESCRIPTION VARCHAR2(250) NULL,
 LAST_UPDATE_TS DATE NOT NULL,
 TRX_TYPE VARCHAR2(25) NULL
);

DROP TABLE DEAD_MSG CASCADE CONSTRAINTS;

CREATE TABLE DEAD_MSG
(
  TECH_MSG_ID VARCHAR2(350 ) NOT NULL PRIMARY KEY,
  SWIFT_MSG_ID VARCHAR2(500 ),
  SWIFT_CORR_ID VARCHAR2(500 ),
  MSG_TYPE VARCHAR2(250 ), 
  MSG_SUB_TYPE VARCHAR2(200 ),
  PAYLOAD VARCHAR2(4000     ),
  TRX_DESCR VARCHAR2(2500    ),
  ORIG_BUNDLE VARCHAR2(2500 ),
  ARRIVED_TS DATE NOT NULL,
  MSG_DESCR VARCHAR2(2500   ),
  TRX_TYPE VARCHAR2(2500 ), 
  DIRECTION VARCHAR2(2000 ),
  UQ_STAMP VARCHAR2(4000     ),
  REASON_COMMENT VARCHAR2(1000   ),
  RESUB_DEST VARCHAR2(100 ),
  RESUB_PAYLOAD VARCHAR2(4000    ), 
  DEAD_SINCE_TS DATE NOT NULL,
  ERROR_REPORT VARCHAR2(4000    ),
  PAYLD CLOB,
  RESUB_PAYLD CLOB,
  APPROVAL_REQUEST VARCHAR2(250 ) 
);

DROP TABLE MSG CASCADE CONSTRAINTS;

CREATE TABLE MSG
(
  TECH_MSG_ID VARCHAR2(35 byte) NOT NULL PRIMARY KEY,
  SWIFT_MSG_ID VARCHAR2(50 byte) NOT NULL, 
  SWIFT_CORR_ID VARCHAR2(50 byte) NOT NULL,
  MSG_TYPE VARCHAR2(25 byte) NOT NULL,
  MSG_SUB_TYPE VARCHAR2(20 byte) NOT NULL,
  PAYLOAD VARCHAR2(4000 byte) NOT NULL,
  TRX_DESCR VARCHAR2(250 byte),
  ORIG_BUNDLE VARCHAR2(250 byte), 
  ARRIVED_TS DATE NOT NULL,
  MSG_DESCR VARCHAR2(250 byte), 
  TRX_TYPE VARCHAR2(25 byte),
  DIRECTION VARCHAR2(20 byte), 
  UQ_STAMP VARCHAR2(1000 byte),
  PAYLD CLOB, 
  CONSTRAINT "MSG_UK21180685752973_1" UNIQUE("UQ_STAMP")
);