--
-- SQL SERVER
--
--Derivative access type in users table
ALTER TABLE [Kodiak].[dbo].[USERS] ADD DERIVATIVES_ACCESS_TYPE INT NULL;
-- Added Properties to accomadate Fund Orders
ALTER TABLE PREALLOCATIONS ADD TRADE_DATE DATETIME NULL;
ALTER TABLE PREALLOCATIONS ADD SETTLEMENT_DATE DATETIME NULL;
ALTER TABLE PREALLOCATIONS ADD TAX_AMOUNT DOUBLE PRECISION NULL;
-- Add CashQuantity columns
ALTER TABLE ALLOCATIONS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE ALLOCATION_EVENTS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE EXECUTIONS ADD ORDER_CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE ORDERS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE ORDER_EVENTS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE INSTRUCTIONS ADD CASH_QUANTITY DOUBLE PRECISION null;
ALTER TABLE PREALLOCATIONS ADD CASH_QUANTITY DOUBLE PRECISION null;
--Update instruments table with Fund Specific columns
ALTER TABLE INSTRUMENTS ADD CUT_OFF_TIME VARCHAR(20) NULL;
ALTER TABLE INSTRUMENTS ADD NUMBER_OF_SETTLEMENT_DAYS_SELL DOUBLE PRECISION null;
ALTER TABLE INSTRUMENTS ADD COMMISSION_BUY DOUBLE PRECISION null;
ALTER TABLE INSTRUMENTS ADD COMMISSION_SELL DOUBLE PRECISION null;
--Update Allocations table with Fund Specific columns
ALTER TABLE ALLOCATIONS ADD EXTERNAL_ACCOUNT_NUMBER VARCHAR(100) NULL;
ALTER TABLE ALLOCATIONS ADD EXTERNAL_ACCOUNT_SSN VARCHAR(20) NULL;
--Update Allocations table with Fund Specific columns
ALTER TABLE PREALLOCATIONS ADD EXTERNAL_ACCOUNT_NUMBER VARCHAR(100) NULL;
ALTER TABLE PREALLOCATIONS ADD EXTERNAL_ACCOUNT_SSN VARCHAR(20) NULL;
--Added Timestamp to Account and Portfolio tables
-- Run this first
ALTER TABLE PORTFOLIOS ADD LAST_UPDATED DATETIME NULL DEFAULT GETDATE();
ALTER TABLE ACCOUNTS ADD LAST_UPDATED DATETIME NULL DEFAULT GETDATE();
ALTER TABLE ACCOUNT_PORTFOLIO ADD LAST_UPDATED DATETIME NULL DEFAULT GETDATE();
-- Then this
CREATE TRIGGER dbo.SetLastUpdatedPortfolios
ON dbo.Portfolios
AFTER UPDATE
AS
BEGIN
IF NOT UPDATE(LAST_UPDATED)
BEGIN
UPDATE t
SET t.LAST_UPDATED = CURRENT_TIMESTAMP
FROM dbo.Portfolios AS t -- not b!
INNER JOIN inserted AS i
ON t.ID_PORTFOLIOS = i.ID_PORTFOLIOS;
END
END
GO
CREATE TRIGGER dbo.SetLastUpdatedAccounts
ON dbo.Accounts
AFTER UPDATE
AS
BEGIN
IF NOT UPDATE(LAST_UPDATED)
BEGIN
UPDATE t
SET t.LAST_UPDATED = CURRENT_TIMESTAMP
FROM dbo.Accounts AS t
INNER JOIN inserted AS i
ON t.ID_ACCOUNTS = i.ID_ACCOUNTS;
END
END
GO
CREATE TRIGGER dbo.SetLastUpdatedAccount_Portfolio
ON dbo.Account_Portfolio
AFTER UPDATE
AS
BEGIN
IF NOT UPDATE(LAST_UPDATED)
BEGIN
UPDATE t
SET t.LAST_UPDATED = CURRENT_TIMESTAMP
FROM dbo.Account_Portfolio AS t
INNER JOIN inserted AS i
ON (t.ACCOUNT_ID = i.ACCOUNT_ID AND t.PORTFOLIO_ID = i.PORTFOLIO_ID);
END
END
GO
-- Add tax amount to Allocation table
ALTER TABLE ALLOCATIONS ADD TAX_AMOUNT DOUBLE PRECISION null;
-- Added discount percent to instructions table
ALTER TABLE INSTRUCTIONS ADD DISCOUNT_PERCENT DOUBLE PRECISION null;
--
-- 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;