CREATE TABLE MBB_CUSTODY.VERIFI_BATCH 
(
  BATCH_ID VARCHAR2(14 BYTE) NOT NULL 
, BATCH_TS TIMESTAMP(6) 
, F_VERIFI_USER_ID VARCHAR2(8 BYTE) 
, F_AUTH_USER_ID VARCHAR2(8 BYTE) 
, AUTH_TS TIMESTAMP(6) 
, F_SEND_USER_ID VARCHAR2(8 BYTE) 
, SENDING_TS TIMESTAMP(6) 
, CONSTRAINT VERIFICATION_BATCH_PK PRIMARY KEY 
  (
    BATCH_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.USER_MAST_AC 
(
  F_USER_ID VARCHAR2(8 BYTE) NOT NULL 
, F_MAST_AC_CODE VARCHAR2(10 BYTE) NOT NULL 
, CONSTRAINT USER_MAST_AC_PK PRIMARY KEY 
  (
    F_USER_ID 
  , F_MAST_AC_CODE 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.TRADE_TO_AMEND 
(
  HOST_TRANS_ID VARCHAR2(10 BYTE) NOT NULL 
, HOST_TRANS_STATUS CHAR(2 BYTE) 
, F_CUST_AC_NO VARCHAR2(12 BYTE) 
, F_STOCK_CODE VARCHAR2(6 BYTE) 
, REF_NO VARCHAR2(16 BYTE) 
, SERIAL_NO VARCHAR2(2 BYTE) 
, TRANS_STATUS VARCHAR2(10 BYTE) 
, TRANS_TYPE VARCHAR2(25 BYTE) 
, COUNTER_NAME VARCHAR2(20 BYTE) 
, FOP CHAR(1 BYTE) 
, ISS CHAR(1 BYTE) 
, SHARES NUMBER(11, 0) 
, AMOUNT NUMBER(13, 0) 
, TRADE_DATE DATE 
, VALUE_DATE DATE 
, SETTLE_DATE DATE 
, SETTLE_TYPE VARCHAR2(7 BYTE) 
, PAY_DATE DATE 
, F_CTPTY_CODE VARCHAR2(12 BYTE) 
, BUY_SELL NUMBER(9, 6) 
, COMM NUMBER(7, 2) 
, ACC_INT NUMBER(13, 2) 
, EXCUM VARCHAR2(3 BYTE) 
, FAIL_DATE DATE 
, F_FAIL_CODE VARCHAR2(3 BYTE) 
, FAIL_REASON NVARCHAR2(30) 
, CANCEL_DATE DATE 
, F_CANCEL_CODE VARCHAR2(3 BYTE) 
, CANCEL_REASON NVARCHAR2(30) 
, F_MODIFIER_USER_ID VARCHAR2(8 BYTE) 
, F_VERIFI_BATCH_ID VARCHAR2(14 BYTE) 
, CONSTRAINT TRADE_PK1 PRIMARY KEY 
  (
    HOST_TRANS_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.TRADE 
(
  TRADE_ID CHAR(14 BYTE) NOT NULL 
, F_CUST_AC_NO VARCHAR2(12 BYTE) 
, F_STOCK_CODE VARCHAR2(6 BYTE) 
, TRADE_TYPE CHAR(1 BYTE) 
, ISS CHAR(1 BYTE) 
, SHARES NUMBER(11, 0) 
, AMOUNT NUMBER(13, 0) 
, FOP CHAR(1 BYTE) 
, REF_NO VARCHAR2(16 BYTE) 
, TRADE_DATE DATE 
, VALUE_DATE DATE 
, F_CTPTY_CODE VARCHAR2(12 BYTE) 
, REMARK NVARCHAR2(300) 
, F_CREATOR_USER_ID VARCHAR2(8 BYTE) 
, F_VERIFI_BATCH_ID VARCHAR2(14 BYTE) 
, BUY_SELL NUMBER(9, 6) 
, COMM NUMBER(7, 2) 
, ACC_INT NUMBER(13, 2) 
, CREATE_TS TIMESTAMP(6) 
, HOST_BATCH_ID VARCHAR2(10 BYTE) 
, HOST_TRANS_ID VARCHAR2(10 BYTE) 
, HOST_TRANS_STATUS CHAR(2 BYTE) 
, CONSTRAINT TRADE_PK PRIMARY KEY 
  (
    TRADE_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.STOCK_COMPANY 
(
  STOCK_CODE VARCHAR2(6 BYTE) NOT NULL 
, COUNTER VARCHAR2(20 BYTE) 
, COUNTER_NAME NVARCHAR2(50) 
, SCRIP_TAG CHAR(1 BYTE) 
, PAR_VALUE NUMBER(5, 2) 
, PAID_UP NUMBER(11, 0) 
, SHARE_PRICE NUMBER(7, 3) 
, PRICE_DATE DATE 
, CATEGORY NVARCHAR2(20) 
, SECTOR NVARCHAR2(30) 
, ISIN_CODE VARCHAR2(12 BYTE) 
, MATURITY_DATE DATE 
, EXPIRY_DATE DATE 
, CONV_MONTH1 NUMBER(2, 0) 
, CONV_MONTH2 NUMBER(2, 0) 
, CONV_MONTH3 NUMBER(2, 0) 
, CONV_MONTH4 NUMBER(2, 0) 
, CURRENCY_CODE CHAR(3 BYTE) 
, COMP_CD VARCHAR2(10 BYTE) 
, CONSTRAINT STOCK_COMPANY_PK PRIMARY KEY 
  (
    STOCK_CODE 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.ORGANIZATION 
(
  ID NUMBER(9, 0) NOT NULL 
, NAME NVARCHAR2(30) 
, CONSTRAINT PK_ORGANIZATION PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.MASTER_AC 
(
  MAST_AC_CODE VARCHAR2(10 BYTE) NOT NULL 
, F_ORG_ID NUMBER(9, 0) 
, CONSTRAINT PK_MASTER_ACC PRIMARY KEY 
  (
    MAST_AC_CODE 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.INCOME_DISTRO 
(
  ID NUMBER(18, 0) NOT NULL 
, F_CUST_AC_NO VARCHAR2(12 BYTE) NOT NULL 
, DN_MAST_AC_CODE VARCHAR2(10 BYTE) 
, F_STOCK_CODE VARCHAR2(6 BYTE) NOT NULL 
, DN_COUNTER NVARCHAR2(20) 
, RECORD_DATE DATE NOT NULL 
, PAR_VALUE NUMBER(5, 2) 
, TAX_RATE NUMBER(5, 2) 
, HOLDINGS NUMBER(11, 0) 
, DIVIDEND_NO VARCHAR2(7 BYTE) 
, WARRANT_NO VARCHAR2(10 BYTE) 
, PAID_BLOCK NUMBER(11, 0) 
, EX_DATE DATE 
, LODG_DATE DATE 
, PAY_DATE DATE 
, YEAR_END DATE 
, CREDIT_DATE DATE 
, GROSS_AMOUNT NUMBER(11, 2) 
, TAX_AMOUNT NUMBER(9, 2) 
, CHARGES NUMBER(7, 2) 
, NET_DIVD NUMBER(13, 2) 
, DIV_RATE NUMBER(7, 4) 
, DIV_RATE_TYPE VARCHAR2(3 BYTE) 
, DIV_TYPE VARCHAR2(15 BYTE) 
, CONSTRAINT INCOME_DISTRO_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.HOST_REPORT 
(
  REPORT_DATE DATE 
, REPORT_NO VARCHAR2(6 BYTE) 
, F_CUST_AC_NO VARCHAR2(12 BYTE) 
, MAST_AC_CODE VARCHAR2(10 BYTE) 
, AC_NAME NVARCHAR2(50) 
, CASH_AC_NO VARCHAR2(12 BYTE) 
, CDS_QFIER_AC VARCHAR2(15 BYTE) 
, HOST_TRANS_ID VARCHAR2(10 BYTE) 
, TRADE_DATE DATE 
, VALUE_DATE DATE 
, SETTLE_DATE DATE 
, EX_CUM CHAR(1 BYTE) 
, COUNTER VARCHAR2(20 BYTE) 
, SCRIPT CHAR(1 BYTE) 
, SHARES NUMBER(11, 0) 
, REG_STN_COM CHAR(3 BYTE) 
, SETTLE_AMOUNT NUMBER(13, 2) 
, FOP_CODE CHAR(1 BYTE) 
, TRANS_REF_NO VARCHAR2(16 BYTE) 
, DELIV_RECIP NVARCHAR2(60) 
, FAIL_CANCEL_DATE DATE 
, FAIL_CANCEL_REASON NVARCHAR2(30) 
, CLASSIFI CHAR(1 BYTE) 
, COUNTER_SECT_CODE CHAR(1 BYTE) 
, PAR_VALUE NUMBER(5, 2) 
, REG_HOLDINGS NUMBER(11, 0) 
, STN_HOLDINGS NUMBER(11, 0) 
, PGV_HOLDINGS NUMBER(11, 0) 
, DESC_REMARKS NVARCHAR2(90) 
, LODGE_DATE DATE 
, EX_DATE DATE 
, PAYABLE_DATE DATE 
, MEETING_TYPE VARCHAR2(3 BYTE) 
, MEETING_VENUE NVARCHAR2(50) 
, AGENDA NVARCHAR2(300) 
, MEETING_DATE DATE 
, MEETING_TIME NVARCHAR2(10) 
, PROXY_DEADLINE DATE 
, SHARE_PRICE NUMBER(7, 3) 
, SHARE_CAPITAL NUMBER(11, 0) 
, SHARE_PERCENT NUMBER(5, 2) 
, PORT_PURCHASE NUMBER(11, 0) 
, PORT_SALES NUMBER(11, 0) 
, CREATE_TS TIMESTAMP(6) 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.CUSTODY_AC 
(
  CUST_AC_NO VARCHAR2(12 BYTE) NOT NULL 
, MAST_AC_CODE VARCHAR2(10 BYTE) 
, CUSTOMER_NAME NVARCHAR2(50) 
, CASH_AC_NO NUMBER(12, 0) 
, CDS_QFIER_AC1 NUMBER(15, 0) 
, CDS_TAG1 CHAR(1 BYTE) 
, OPEN_DATE DATE 
, CLOSE_DATE DATE 
, OMNI_TAG CHAR(1 BYTE) 
, OMNI_PARENT VARCHAR2(12 BYTE) 
, CONSTRAINT PK_CUSTODY_ACC PRIMARY KEY 
  (
    CUST_AC_NO 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.COUNTER_PARTY 
(
  CTPTY_CODE VARCHAR2(12 BYTE) NOT NULL 
, CTPTY_NAME NVARCHAR2(60) 
, ADAM_CODE VARCHAR2(6 BYTE) 
, CONSTRAINT COUNTER_PARTY_PK PRIMARY KEY 
  (
    CTPTY_CODE 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.CORP_MEETING_PER_CUST 
(
  ID NUMBER(18, 0) 
, F_CUST_AC_NO VARCHAR2(12 BYTE) NOT NULL 
, DN_MAST_AC_CODE VARCHAR2(10 BYTE) 
, F_STOCK_CODE VARCHAR2(6 BYTE) NOT NULL 
, DN_COUNTER VARCHAR2(20 BYTE) 
, RECORD_DATE DATE NOT NULL 
, TYPE VARCHAR2(30 BYTE) 
, MEETING_DATE DATE NOT NULL 
, MEETING_TIME VARCHAR2(10 BYTE) 
, VENUE NVARCHAR2(50) 
, PROXY_DEADLINE DATE 
, DN_HOLDINGS NUMBER(11, 0) 
, DN_REG NUMBER(11, 1) 
, DN_STN NUMBER(11, 0) 
, DESC1 NVARCHAR2(35) 
, DESC2 NVARCHAR2(35) 
, DESC3 NVARCHAR2(35) 
, DESC5 NVARCHAR2(35) 
, CREATE_TS TIMESTAMP(6) 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.CORP_MEETING 
(
  ID NUMBER(18, 0) 
, F_STOCK_CODE VARCHAR2(6 BYTE) NOT NULL 
, MEETING_DATE DATE NOT NULL 
, MEETING_TIME VARCHAR2(10 BYTE) 
, SEQ_NO NUMBER(6, 0) NOT NULL 
, TYPE VARCHAR2(30 BYTE) 
, VENUE NVARCHAR2(50) 
, PROXY_DEADLINE DATE 
, NARRATIVE NVARCHAR2(300) 
, DN_COUNTER VARCHAR2(20 BYTE) 
, CREATE_TS TIMESTAMP(6) 
, CONSTRAINT CORP_MEETING_PK PRIMARY KEY 
  (
    F_STOCK_CODE 
  , SEQ_NO 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.CORP_ACTION_PER_CUST 
(
  ID NUMBER(18, 0) NOT NULL 
, F_CUST_AC_NO VARCHAR2(12 BYTE) NOT NULL 
, DN_MAST_AC_CODE VARCHAR2(10 BYTE) 
, F_STOCK_CODE VARCHAR2(6 BYTE) NOT NULL 
, DN_COUNTER NVARCHAR2(20) 
, RECORD_DATE DATE NOT NULL 
, LODGE_DATE DATE 
, EX_DATE DATE 
, PAYABLE_DATE DATE 
, LISTING_DATE DATE 
, YEAR_END DATE 
, DESC1 NVARCHAR2(35) 
, DESC2 NVARCHAR2(35) 
, DESC3 NVARCHAR2(35) 
, DESC4 NVARCHAR2(35) 
, ACCEPTANCE_DATE DATE 
, DN_PAR_VALUE NUMBER(5, 2) 
, DN_TAX_RATE NUMBER(5, 2) 
, DN_HOLDINGS NUMBER(11, 0) 
, DN_REG NUMBER(11, 0) 
, DN_STN NUMBER(11, 0) 
, DN_PGV NUMBER(11, 0) 
, REC_TYPE CHAR(1 BYTE) 
, ALERT_TAG CHAR(1 BYTE) 
, CREATE_TS TIMESTAMP(6) 
, CONSTRAINT CORP_ACTION_PER_CUST_PK1 PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.CORP_ACTION 
(
  ID NUMBER(18, 0) NOT NULL 
, F_STOCK_CODE VARCHAR2(6 BYTE) NOT NULL 
, LODGE_DATE DATE 
, EX_DATE DATE 
, FINANCIAL_DATE DATE 
, PAYABLE_DATE DATE 
, LISTING_DATE DATE 
, DESC1 NVARCHAR2(30) 
, DESC2 NVARCHAR2(30) 
, DESC3 NVARCHAR2(30) 
, RI_TRADE_START DATE 
, RI_TRADE_END DATE 
, PAL_SPLIT_END DATE 
, ACCEPTANCE_DATE DATE 
, SHARE_PRICE NUMBER(7, 3) 
, DN_COUNTER VARCHAR2(20 BYTE) 
, CREATE_TS TIMESTAMP(6) 
, CONSTRAINT CORP_ACTION_PK PRIMARY KEY 
  (
    ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.APP_USER 
(
  USER_ID VARCHAR2(8 BYTE) NOT NULL 
, USERNAME VARCHAR2(20 BYTE) 
, PASSWORD VARCHAR2(20 BYTE) 
, FULL_NAME NVARCHAR2(50) 
, ROLES VARCHAR2(3 BYTE) 
, F_ORG_ID NUMBER(9, 0) 
, CONSTRAINT APP_USER_PK PRIMARY KEY 
  (
    USER_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.APP_CALENDAR 
(
  THE_DATE DATE NOT NULL 
, HOLIDAY CHAR(1 BYTE) 
, DESCRIPTION NVARCHAR2(30) 
, CONSTRAINT APP_CALENDAR_PK PRIMARY KEY 
  (
    THE_DATE 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE MBB_CUSTODY.AMEND_VERIFI_BATCH 
(
  BATCH_ID VARCHAR2(14 BYTE) NOT NULL 
, BATCH_TS TIMESTAMP(6) 
, F_VERIFI_USER_ID VARCHAR2(8 BYTE) 
, F_AUTH_USER_ID VARCHAR2(8 BYTE) 
, AUTH_TS TIMESTAMP(6) 
, F_SEND_USER_ID VARCHAR2(8 BYTE) 
, SENDING_TS TIMESTAMP(6) 
, CONSTRAINT VERIFICATION_BATCH_PK1 PRIMARY KEY 
  (
    BATCH_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

ALTER TABLE MBB_CUSTODY.STOCK_COMPANY
ADD CONSTRAINT STOCK_COMPANY_UK UNIQUE 
(
  COUNTER 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.VERIFI_BATCH
ADD CONSTRAINT FK_VERIFI_BATCH_APP_USER1 FOREIGN KEY
(
  F_VERIFI_USER_ID 
)
REFERENCES MBB_CUSTODY.APP_USER
(
  USER_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.VERIFI_BATCH
ADD CONSTRAINT FK_VERIFI_BATCH_APP_USER2 FOREIGN KEY
(
  F_AUTH_USER_ID 
)
REFERENCES MBB_CUSTODY.APP_USER
(
  USER_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.VERIFI_BATCH
ADD CONSTRAINT FK_VERIFI_BATCH_APP_USER3 FOREIGN KEY
(
  F_SEND_USER_ID 
)
REFERENCES MBB_CUSTODY.APP_USER
(
  USER_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.USER_MAST_AC
ADD CONSTRAINT FK_USER_MAST_AC_APP_USER FOREIGN KEY
(
  F_USER_ID 
)
REFERENCES MBB_CUSTODY.APP_USER
(
  USER_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.USER_MAST_AC
ADD CONSTRAINT FK_USER_MAST_AC_MASTER_AC FOREIGN KEY
(
  F_MAST_AC_CODE 
)
REFERENCES MBB_CUSTODY.MASTER_AC
(
  MAST_AC_CODE 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.TRADE_TO_AMEND
ADD CONSTRAINT FK_TRADE_2A_APP_USER FOREIGN KEY
(
  F_MODIFIER_USER_ID 
)
REFERENCES MBB_CUSTODY.APP_USER
(
  USER_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.TRADE_TO_AMEND
ADD CONSTRAINT FK_TRADE_2A_COUNTER_PARTY FOREIGN KEY
(
  F_CTPTY_CODE 
)
REFERENCES MBB_CUSTODY.COUNTER_PARTY
(
  CTPTY_CODE 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.TRADE_TO_AMEND
ADD CONSTRAINT FK_TRADE_2A_CUSTODY_AC FOREIGN KEY
(
  F_CUST_AC_NO 
)
REFERENCES MBB_CUSTODY.CUSTODY_AC
(
  CUST_AC_NO 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.TRADE_TO_AMEND
ADD CONSTRAINT FK_TRADE_2A_STOCK_COMPANY FOREIGN KEY
(
  F_STOCK_CODE 
)
REFERENCES MBB_CUSTODY.STOCK_COMPANY
(
  STOCK_CODE 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.TRADE_TO_AMEND
ADD CONSTRAINT FK_TRADE_2A_VERIFI_BATCH FOREIGN KEY
(
  F_VERIFI_BATCH_ID 
)
REFERENCES MBB_CUSTODY.AMEND_VERIFI_BATCH
(
  BATCH_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.TRADE
ADD CONSTRAINT FK_OWN_TRADE_VERIFI_BATCH FOREIGN KEY
(
  F_VERIFI_BATCH_ID 
)
REFERENCES MBB_CUSTODY.VERIFI_BATCH
(
  BATCH_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.TRADE
ADD CONSTRAINT FK_TRADE_APP_USER FOREIGN KEY
(
  F_CREATOR_USER_ID 
)
REFERENCES MBB_CUSTODY.APP_USER
(
  USER_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.TRADE
ADD CONSTRAINT FK_TRADE_COUNTER_PARTY FOREIGN KEY
(
  F_CTPTY_CODE 
)
REFERENCES MBB_CUSTODY.COUNTER_PARTY
(
  CTPTY_CODE 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.TRADE
ADD CONSTRAINT FK_TRADE_CUSTODY_AC FOREIGN KEY
(
  F_CUST_AC_NO 
)
REFERENCES MBB_CUSTODY.CUSTODY_AC
(
  CUST_AC_NO 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.TRADE
ADD CONSTRAINT FK_TRADE_STOCK_COMPANY FOREIGN KEY
(
  F_STOCK_CODE 
)
REFERENCES MBB_CUSTODY.STOCK_COMPANY
(
  STOCK_CODE 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.MASTER_AC
ADD CONSTRAINT FK_MASTER_AC_ORG FOREIGN KEY
(
  F_ORG_ID 
)
REFERENCES MBB_CUSTODY.ORGANIZATION
(
  ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.INCOME_DISTRO
ADD CONSTRAINT FK_INCOME_DISTRO_CUSTODY_AC FOREIGN KEY
(
  F_CUST_AC_NO 
)
REFERENCES MBB_CUSTODY.CUSTODY_AC
(
  CUST_AC_NO 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.INCOME_DISTRO
ADD CONSTRAINT FK_INCOME_DISTRO_STOCK_COMPANY FOREIGN KEY
(
  F_STOCK_CODE 
)
REFERENCES MBB_CUSTODY.STOCK_COMPANY
(
  STOCK_CODE 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.CUSTODY_AC
ADD CONSTRAINT FK_CUSTODY_AC_MASTER_ACC FOREIGN KEY
(
  MAST_AC_CODE 
)
REFERENCES MBB_CUSTODY.MASTER_AC
(
  MAST_AC_CODE 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.CUSTODY_AC
ADD CONSTRAINT FK_CUSTODY_AC_RECURSIVE FOREIGN KEY
(
  OMNI_PARENT 
)
REFERENCES MBB_CUSTODY.CUSTODY_AC
(
  CUST_AC_NO 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.CORP_MEETING_PER_CUST
ADD CONSTRAINT FK_CORP_MEETING_PC_CUSTODY_AC FOREIGN KEY
(
  F_CUST_AC_NO 
)
REFERENCES MBB_CUSTODY.CUSTODY_AC
(
  CUST_AC_NO 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.CORP_MEETING_PER_CUST
ADD CONSTRAINT FK_CORP_MEET_PER_CUST_STOCK_C1 FOREIGN KEY
(
  F_STOCK_CODE 
)
REFERENCES MBB_CUSTODY.STOCK_COMPANY
(
  STOCK_CODE 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.CORP_MEETING
ADD CONSTRAINT FK_CORP_MEETING_STOCK_COMPANY FOREIGN KEY
(
  F_STOCK_CODE 
)
REFERENCES MBB_CUSTODY.STOCK_COMPANY
(
  STOCK_CODE 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.CORP_ACTION_PER_CUST
ADD CONSTRAINT FK_CORP_ACTION_PC_CUSTODY_AC FOREIGN KEY
(
  F_CUST_AC_NO 
)
REFERENCES MBB_CUSTODY.CUSTODY_AC
(
  CUST_AC_NO 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.CORP_ACTION_PER_CUST
ADD CONSTRAINT FK_CORP_ACTION_PER_CUST_STOCK1 FOREIGN KEY
(
  F_STOCK_CODE 
)
REFERENCES MBB_CUSTODY.STOCK_COMPANY
(
  STOCK_CODE 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.CORP_ACTION
ADD CONSTRAINT FK_CORP_ACTION_STOCK_COMPANY FOREIGN KEY
(
  F_STOCK_CODE 
)
REFERENCES MBB_CUSTODY.STOCK_COMPANY
(
  STOCK_CODE 
)
DISABLE;

ALTER TABLE MBB_CUSTODY.APP_USER
ADD CONSTRAINT FK_APP_USER_ORG FOREIGN KEY
(
  F_ORG_ID 
)
REFERENCES MBB_CUSTODY.ORGANIZATION
(
  ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.AMEND_VERIFI_BATCH
ADD CONSTRAINT FK_AMEND_VERIFI_BATCH_APP_USE1 FOREIGN KEY
(
  F_VERIFI_USER_ID 
)
REFERENCES MBB_CUSTODY.APP_USER
(
  USER_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.AMEND_VERIFI_BATCH
ADD CONSTRAINT FK_AMEND_VERIFI_BATCH_APP_USE2 FOREIGN KEY
(
  F_AUTH_USER_ID 
)
REFERENCES MBB_CUSTODY.APP_USER
(
  USER_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.AMEND_VERIFI_BATCH
ADD CONSTRAINT FK_AMEND_VERIFI_BATCH_APP_USE3 FOREIGN KEY
(
  F_SEND_USER_ID 
)
REFERENCES MBB_CUSTODY.APP_USER
(
  USER_ID 
)
ENABLE;

ALTER TABLE MBB_CUSTODY.TRADE_TO_AMEND
ADD CONSTRAINT CHK_TRADE_IS_ISS CHECK 
(ISS IN ('Y', 'N'))
ENABLE;

ALTER TABLE MBB_CUSTODY.TRADE
ADD CONSTRAINT CHK_TRADE_ISS CHECK 
(ISS IN ('Y', 'N'))
ENABLE;

ALTER TABLE MBB_CUSTODY.TRADE
ADD CONSTRAINT CHK_TRADE_TYPE CHECK 
(TRADE_TYPE IN ('P', 'S'))
ENABLE;

COMMENT ON COLUMN MBB_CUSTODY.TRADE_TO_AMEND.HOST_TRANS_STATUS IS 'Transaction Status: Authorised / Confirmed / Settled / Open ';

COMMENT ON COLUMN MBB_CUSTODY.TRADE_TO_AMEND.EXCUM IS 'EX OR CUM';

COMMENT ON COLUMN MBB_CUSTODY.APP_USER.ROLES IS 'A String containing any of ''I'' for inintator, ''V'' for verifier and ''A'' for authorizer.';
