Database changes 1.9.11 Oracle

--

-- ORACLE

--

----Derivative access type in users table
ALTER TABLE USERS ADD DERIVATIVES_ACCESS_TYPE NUMBER(10,0) NULL;

-- Added Properties to accomadate Fund Orders
ALTER TABLE PREALLOCATIONS ADD TRADE_DATE DATE NULL;
ALTER TABLE PREALLOCATIONS ADD SETTLEMENT_DATE DATE NULL;
ALTER TABLE PREALLOCATIONS ADD TAX_AMOUNT FLOAT NULL;

-- Add CashQuantity columns
ALTER TABLE ALLOCATIONS ADD CASH_QUANTITY FLOAT NULL;
ALTER TABLE ALLOCATION_EVENTS ADD CASH_QUANTITY FLOAT NULL;
ALTER TABLE EXECUTIONS ADD ORDER_CASH_QUANTITY FLOAT NULL;
ALTER TABLE ORDERS ADD CASH_QUANTITY FLOAT NULL;
ALTER TABLE ORDER_EVENTS ADD CASH_QUANTITY FLOAT NULL;
ALTER TABLE INSTRUCTIONS ADD CASH_QUANTITY FLOAT NULL;
ALTER TABLE PREALLOCATIONS ADD CASH_QUANTITY FLOAT NULL;

--Update instruments table with Fund Specific columns
ALTER TABLE INSTRUMENTS ADD CUT_OFF_TIME VARCHAR2(20) NULL;
ALTER TABLE INSTRUMENTS ADD NUMBER_OF_SETTLEMENT_DAYS_SELL FLOAT NULL;
ALTER TABLE INSTRUMENTS ADD COMMISSION_BUY FLOAT NULL;
ALTER TABLE INSTRUMENTS ADD COMMISSION_SELL FLOAT NULL;

--Update Allocations table with Fund Specific columns
ALTER TABLE ALLOCATIONS ADD EXTERNAL_ACCOUNT_NUMBER VARCHAR2(100) NULL;
ALTER TABLE ALLOCATIONS ADD EXTERNAL_ACCOUNT_SSN VARCHAR2(20) NULL;

--Update Allocations table with Fund Specific columns
ALTER TABLE PREALLOCATIONS ADD EXTERNAL_ACCOUNT_NUMBER VARCHAR2(100) NULL;
ALTER TABLE PREALLOCATIONS ADD EXTERNAL_ACCOUNT_SSN VARCHAR2(20) NULL;

--Added Timestamp to Account and Portfolio tables
-- Run this first
ALTER TABLE PORTFOLIOS ADD LAST_UPDATED TIMESTAMP NULL;
ALTER TABLE ACCOUNTS ADD LAST_UPDATED TIMESTAMP NULL;
ALTER TABLE ACCOUNT_PORTFOLIO ADD LAST_UPDATED TIMESTAMP NULL;

CREATE OR REPLACE TRIGGER SET_LAST_UPDATED_PORTFOLIOS
BEFORE INSERT OR UPDATE
ON PORTFOLIOS
FOR EACH ROW
DECLARE
BEGIN
:new.LAST_UPDATED := sysdate;
END;

CREATE OR REPLACE TRIGGER SET_LAST_UPDATED_ACCOUNTS
BEFORE INSERT OR UPDATE
ON ACCOUNTS
FOR EACH ROW
DECLARE
BEGIN
:new.LAST_UPDATED := sysdate;
END;

CREATE OR REPLACE TRIGGER SET_LAST_UPDATED_ACC_PORTF
BEFORE INSERT OR UPDATE
ON ACCOUNT_PORTFOLIO
FOR EACH ROW
DECLARE
BEGIN
:new.LAST_UPDATED := sysdate;
END;

-- Add tax amount to Allocation table
ALTER TABLE ALLOCATIONS ADD TAX_AMOUNT FLOAT NULL;

-- Added discount percent to instructions table
ALTER TABLE INSTRUCTIONS ADD DISCOUNT_PERCENT FLOAT NULL;

-- Add an instrument to the allocations table.

ALTER TABLE ALLOCATIONS ADD INSTRUMENT_ID NUMBER(10,0) NULL;
 
ALTER TABLE ALLOCATIONS 
ADD CONSTRAINT ALLOC_INSTRUMENT_FK 
FOREIGN KEY (INSTRUMENT_ID) 
REFERENCES INSTRUMENTS(ID_INSTRUMENTS);