Z-Archive » KFH Project Archive » IBFT SAN Via IB

Added:

Sequence: IBFT_TRANSFER_SEQ

DROP SEQUENCE BVUSERPRD.IBFT_TRANSFER_SEQ;

CREATE SEQUENCE BVUSERPRD.IBFT_TRANSFER_SEQ
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

Table IB_IBFT_TRANSFER and trigger IBFT_TRANSFER_TRIGGER:

DROP TABLE BVUSERPRD.IB_IBFT_TRANSFER CASCADE CONSTRAINTS;

CREATE TABLE BVUSERPRD.IB_IBFT_TRANSFER
(
  OID                     NUMBER,
  TRNX_ID                 VARCHAR2(20 BYTE)     NOT NULL,
  TRNX_TYPE               VARCHAR2(255 BYTE)    NOT NULL,
  TRNX_DATE               DATE                  NOT NULL,
  USER_ID                 INTEGER               NOT NULL,
  FROM_ACCOUNT_NO         VARCHAR2(64 BYTE)     NOT NULL,
  BENEFICIARY_ACCOUNT_NO  VARCHAR2(64 BYTE)     NOT NULL,
  FROM_CURRENCY           VARCHAR2(3 BYTE),
  TO_CURRENCY             VARCHAR2(3 BYTE),
  FROM_ACC_CRT_DT         DATE,
  AMOUNT                  NUMBER(18,6),
  LOCAL_AMOUNT            NUMBER(18,6),
  PAYMENT_DETAILS         VARCHAR2(255 BYTE),
  BENEFICIARY_NAME        VARCHAR2(64 BYTE)     NOT NULL,
  BENEFICIARY_BANK_NAME   VARCHAR2(64 BYTE)     NOT NULL,
  BENEFICIARY_BANK_ID     VARCHAR2(10 BYTE)     NOT NULL,
  BENE_TRNX_REF_NO        VARCHAR2(64 BYTE),
  BENE_EMAIL              VARCHAR2(255 BYTE),
  DESCRIPTION             VARCHAR2(255 BYTE),
  EFFECTIVE_DATE          VARCHAR2(10 BYTE),
  SERVICE_CODE            VARCHAR2(10 BYTE),
  BANK_ROUTE_ID           VARCHAR2(10 BYTE),
  STATUS                  VARCHAR2(64 BYTE)     NOT NULL,
  REASON                  VARCHAR2(255 BYTE),
  IBFT_AUDIT_NO           VARCHAR2(20 BYTE),
  HOST_REFERENCE_NO       VARCHAR2(20 BYTE),
  REMOTE_IP               VARCHAR2(64 BYTE),
  OTHER1                  VARCHAR2(50 BYTE),
  OTHER2                  VARCHAR2(50 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;

CREATE OR REPLACE TRIGGER BVUSERPRD.IBFT_TRANSFER_TRIGGER
before insert ON BVUSERPRD.IB_IBFT_TRANSFER for each row
begin
select IBFT_TRANSFER_SEQ.nextval into :new.OID from dual;
END;
/

ALTER TABLE BVUSERPRD.IB_IBFT_TRANSFER ADD (
  UNIQUE (OID)
    USING INDEX 
    TABLESPACE USERS
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));