------------------------
-- DDL for New Tables --
------------------------

-- TRX_CUSTOMER_PERSONAL_UPLOAD_FILES
CREATE TABLE AVANTRADE.TRX_CUSTOMER_PERSONAL_UPLOAD_FILES (
	FILE_UPLOAD_ID RAW(16) NULL,
	FILE_NAME VARCHAR2(255) NULL,
	FILE_DESCRIPTION VARCHAR2(255) NULL,
	UPLOAD_BY VARCHAR2(30) NULL,
	UPLOAD_DATE TIMESTAMP NULL,
	LAST_APPROVED_BY VARCHAR2(30) NULL,
	LAST_APPROVED_DATE TIMESTAMP NULL,
	TRX_STATUS CHAR(3) NULL
);

-- TRX_CUSTOMER_PERSONAL_UPLOAD_DETAILS
CREATE TABLE AVANTRADE.TRX_CUSTOMER_PERSONAL_UPLOAD_DETAILS (
	CIF NUMBER(15,0) NULL,
	CIF_BANK VARCHAR2(15) NULL,
	BRANCH_ID VARCHAR2(50) NULL,
	FIRST_NAME VARCHAR2(60) NULL,
	SHORT_NAME VARCHAR2(60) NULL,
	GENDER NUMBER(2,0) NULL,
	BIRTH_DATE DATE NULL,
	CITIZENSHIP VARCHAR2(20) NULL,
	SEGMENT VARCHAR2(20) NULL,
	INCOME VARCHAR2(16) NULL,
	PROFESSION VARCHAR2(20) NULL,
	EDUCATION_BACKGROUND VARCHAR2(20) NULL,
	RELIGION VARCHAR2(20) NULL,
	ALPHA3_CODE CHAR(5) NULL,
	COUNTRY_CODE CHAR(2) NULL,
	MOBILE_PHONE_NO VARCHAR2(50) NULL,
	EMAIL_ADDRESS VARCHAR2(100) NULL,
	RESIDENT_PHONE_NO VARCHAR2(50) NULL,
	ID_NUMBER VARCHAR2(100) NULL,
	ID_OWNER_NAME VARCHAR2(100) NULL,
	ID_TYPE_PERSONAL VARCHAR2(100) NULL,
	ISSUED_DATE DATE NULL,
	EXPIRED_DATE DATE NULL,
	PLACE_OF_BIRTH VARCHAR2(100) NULL,
	MARITAL_STATUS VARCHAR2(100) NULL,
	SPOUSE_NAME VARCHAR2(100) NULL,
	SPOUSE_DATE_OF_BIRTH DATE NULL,
	INTEREST VARCHAR2(100) NULL,
	RELATIONSHIP_OFFICER_CODE VARCHAR2(100) NULL,
	NPWP VARCHAR2(100) NULL,
	RISK_PROFILE VARCHAR2(100) NULL,
	CUSTOMER_RISK_PROFILE VARCHAR2(100) NULL,
	INVESTMENT_PURPOSE VARCHAR2(100) NULL,
	SOURCE_OF_FUNDS VARCHAR2(100) NULL,
	ADDRESS_1 VARCHAR2(255) NULL,
	ADDRESS_2 VARCHAR2(255) NULL,
	ADDRESS_3 VARCHAR2(255) NULL,
	ADDRESS_4 VARCHAR2(255) NULL,
	PROVINCE VARCHAR2(100) NULL,
	CITY VARCHAR2(100) NULL,
	POSTAL_CODE VARCHAR2(100) NULL,
	SETTLEMENT_ACCOUNT_NUMBER_IDR VARCHAR2(100) NULL
);

ALTER TABLE AVANTRADE.TRX_CUSTOMER_PERSONAL_UPLOAD_DETAILS ADD LAST_NAME VARCHAR2(60) NULL;
ALTER TABLE AVANTRADE.TRX_CUSTOMER_PERSONAL_UPLOAD_DETAILS ADD IS_EMPLOYEE NUMBER(1,0) NULL;
ALTER TABLE AVANTRADE.TRX_CUSTOMER_PERSONAL_UPLOAD_DETAILS ADD STATEMENT_TYPE NUMBER(2,0) NULL;
ALTER TABLE AVANTRADE.TRX_CUSTOMER_PERSONAL_UPLOAD_DETAILS ADD TIN VARCHAR2(100) NULL;
ALTER TABLE AVANTRADE.TRX_CUSTOMER_PERSONAL_UPLOAD_DETAILS MODIFY CITIZENSHIP NUMBER(2,0);
ALTER TABLE AVANTRADE.TRX_CUSTOMER_PERSONAL_UPLOAD_DETAILS ADD UPLOAD_DETAIL_ID RAW(16) NULL;


-- TRX_SUBSCRIPTION_UPLOAD_FILES
CREATE TABLE AVANTRADE.TRX_SUBSCRIPTION_UPLOAD_FILES (
	FILE_UPLOAD_ID RAW(16) NULL,
	FILE_NAME VARCHAR2(255) NULL,
	FILE_DESCRIPTION VARCHAR2(255) NULL,
	UPLOAD_BY VARCHAR2(30) NULL,
	UPLOAD_DATE TIMESTAMP NULL,
	LAST_APPROVED_BY VARCHAR2(30) NULL,
	LAST_APPROVED_DATE TIMESTAMP NULL,
	TRX_STATUS CHAR(3) NULL
);

-- TRX_SUBSCRIPTION_UPLOAD_DETAILS
CREATE TABLE AVANTRADE.TRX_SUBSCRIPTION_UPLOAD_DETAILS (
	CUSTOMER_ID NUMBER(15,0) NULL,
	INV_ACCOUNT_ID VARCHAR2(20) NULL,
	TRX_BRANCH_ID VARCHAR2(50) NULL,
	SALES_ID VARCHAR2(100) NULL,
	SALES_BRANCH_ID VARCHAR2(100) NULL,
	INPUTTER_BRANCH_ID VARCHAR2(100) NULL,
	PRODUCT_ID VARCHAR2(100) NULL,
	TRX_CURRENCY CHAR(3) NULL,
	TRX_DATE TIMESTAMP NULL,
	PRICE_DATE TIMESTAMP NULL,
	TRX_AMOUNT NUMBER(30,10) NULL,
	FUND_SOURCE VARCHAR2(100) NULL,
	CALCULATION_TYPE NUMBER(2,0) NULL,
	APPLIED_FEE_TYPE NUMBER(2,0) NULL,
	FEE_STRUCTURE_ID VARCHAR2(100) NULL,
	ORIG_FEE_RATE NUMBER(11,10) NULL,
	NEW_FEE_RATE NUMBER(11,10) NULL,
	FEE_AMOUNT NUMBER(30,10) NULL,
	CHANNEL NUMBER(1,0) NULL
);

ALTER TABLE AVANTRADE.TRX_SUBSCRIPTION_UPLOAD_DETAILS ADD FILE_UPLOAD_ID RAW(16) NULL;
ALTER TABLE AVANTRADE.TRX_SUBSCRIPTION_UPLOAD_DETAILS ADD UPLOAD_DETAIL_ID RAW(16) NULL;

-----------------------------
-- DDL for Existing Tables --
-----------------------------

-- TRX_UT_TRANSACTIONS
ALTER TABLE AVANTRADE.TRX_UT_TRANSACTIONS 
ADD (SUBSCRIPTION_FILE_UPLOAD_DETAIL_ID RAW(16) );

-- MST_INVESTMENT_ACCOUNTS
ALTER TABLE MST_INVESTMENT_ACCOUNTS
ADD (ONE_TIME_SUBS_FLAG NUMBER(2) );

COMMENT ON COLUMN MST_INVESTMENT_ACCOUNTS.ONE_TIME_SUBS_FLAG IS '0=false, -1=true';
