ALTER TABLE Hris_Employee_Loan_Request
ADD REPAYMENT_MONTHS NUMBER(3,0);

CREATE TABLE HRIS_LOAN_PAYMENT_DETAIL
(
PAYMENT_ID NUMBER(7,0) NOT NULL PRIMARY KEY,
LOAN_REQUEST_ID NOT NULL REFERENCES Hris_Employee_Loan_Request (LOAN_REQUEST_ID),
SNO NUMBER(3,0) NOT NULL ,
FROM_DATE DATE ,
TO_DATE DATE,
AMOUNT FLOAT(126),
PAID_FLAG CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(PAID_FLAG IN ('Y','N')),
MONTH_NO NUMBER(7,0),
INTEREST_AMOUNT FLOAT(126),
PRINCIPLE_AMOUNT FLOAT(126),
DAYS NUMBER(2,0),
VOUCHER_NO VARCHAR2(100 BYTE),
SHEET_NO NUMBER(7,0),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS IN ('E','D')),
REMARKS VARCHAR2(300 BYTE),
CREATED_DT DATE DEFAULT TRUNC(SYSDATE),
CREATED_BY NUMBER(7,0),
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_DT DATE,
DELETED_BY NUMBER(7,0)
);

CREATE TABLE HRIS_LOAN_CASH_PAYMENT(
ID NUMBER(7,0) NOT NULL,
PAYMENT_DATE DATE,
LOAN_REQ_ID NUMBER(7,0) NOT NULL REFERENCES HRIS_EMPLOYEE_LOAN_REQUEST (LOAN_REQUEST_ID),
PAYMENT_AMOUNT FLOAT(126) DEFAULT 0,
PRINCIPLE_AMOUNT FLOAT(126) DEFAULT 0,
INTEREST FLOAT(126) DEFAULT 0,
RECEIPT_NO VARCHAR(300) DEFAULT NULL,
REMARKS VARCHAR2(500)
);

ALTER TABLE HRIS_EMPLOYEE_LOAN_REQUEST ADD LOAN_STATUS VARCHAR(6) DEFAULT 'OPEN';

ALTER TABLE HRIS_EMPLOYEE_LOAN_REQUEST ADD INTEREST_RATE FLOAT(126);

ALTER TABLE HRIS_EMPLOYEE_LOAN_REQUEST ADD CONSTRAINT CHECK_LOAN_STATUS
CHECK(LOAN_STATUS IN ('OPEN', 'CLOSED'));

ALTER TABLE HRIS_LOAN_MASTER_SETUP
ADD PAY_ID_AMT NUMBER(7,0);

ALTER TABLE HRIS_LOAN_MASTER_SETUP
ADD PAY_ID_INT NUMBER(7,0);

ALTER TABLE HRIS_LOAN_MASTER_SETUP
ADD IS_RATE_FLEXIBLE CHAR(1) CHECK (IS_RATE_FLEXIBLE IN ('Y', 'N'));

alter table hris_loan_master_setup modify max_amount float(126);
alter table hris_loan_master_setup modify (max_amount null);
alter table hris_loan_master_setup add valid_from date;
alter table hris_loan_master_setup add valid_upto date;
alter table hris_loan_master_setup add loan_type number;
alter table hris_loan_master_setup add issued_by varchar2(10);
alter table hris_loan_master_setup add eligible_service_period number;
alter table hris_loan_master_setup add max_issue_time number;

CREATE TABLE HRIS_LOAN_MASTER_FILES 
(  
"FILE_ID" NUMBER(7,0) PRIMARY KEY, 
"FILE_NAME" VARCHAR2(255 BYTE), 
"FILE_IN_DIR_NAME" VARCHAR2(4000 BYTE), 
"UPLOADED_DATE" DATE DEFAULT TRUNC(SYSDATE), 
"LOAN_ID" NUMBER(7,0),
"STATUS" char(1) DEFAULT 'E',
CONSTRAINT FK_loanfileid FOREIGN KEY (LOAN_ID)
REFERENCES HRIS_LOAN_MASTER_SETUP(LOAN_ID)
);


alter table hris_loan_master_setup add (ledger_code varchar(10));