alter table HRIS_EMPLOYEES add (
	 CHECKED SHORTTEXT(1),
	 CHECKED_BY INTEGER,
	 CHECKED_DT DATE,
	 VERIFIED SHORTTEXT(1),
	 VERIFIED_BY INTEGER,
	 VERIFIED_DT DATE
	 );

alter table HRIS_EMPLOYEES add constraint
    checked_flag CHECK ( CHECKED in ( 'Y','N'));

alter table HRIS_EMPLOYEES add constraint
    verified_flag CHECK ( VERIFIED in ( 'Y','N'));

alter table HRIS_ROLES add (
	CHECKER SHORTTEXT(1) DEFAULT 'N' NOT NULL,
	MAKER SHORTTEXT(1) DEFAULT 'N' NOT NULL
);

ALTER TABLE HRIS_ROLES ADD CONSTRAINT checker_flag CHECK(CHECKER IN ('Y','N'));

ALTER TABLE HRIS_ROLES ADD CONSTRAINT maker_flag CHECK(MAKER IN ('Y','N'));

----------noc leave start-----------
alter table HRIS_LEAVE_MASTER_SETUP add (
SERVICE_PERIOD_REQUIRED INTEGER,
DAYS_MATURED INTEGER,
MIN_DAYS_FOR_ENCASH INT,
IMPACT_GRADE SHORTTEXT(1) DEFAULT 'N' ,
COUNT_AS_SERVICE_PERIOD SHORTTEXT(1) DEFAULT 'Y'
);

alter table HRIS_LEAVE_MASTER_SETUP add
    CONSTRAINT gradeImpactCheck CHECK( IMPACT_GRADE IN ('Y', 'N'));

alter table HRIS_LEAVE_MASTER_SETUP
    add CONSTRAINT countService CHECK( COUNT_AS_SERVICE_PERIOD IN ('Y', 'N'));
----------noc leave end-----------

-----------TRAVEL---------
create table hris_position_class_map(
    id number(7,0),
    position_id number(7,0),
    class_id number (7,0),
    STATUS shorttext(1) NOT NULL,
    CONSTRAINT STS_CHK CHECK(STATUS  IN ('E','D')), 
    CREATED_BY NUMBER(7,0),
    APPROVED_BY NUMBER(7,0),
    APPROVED_DATE DATE,
    MODIFIED_BY NUMBER(7,0),
    CREATED_DT DATE,
    CHECKED_BY NUMBER(7,0),
    CHECKED_DT DATE,
    MODIFIED_DT DATE,
    DELETED_BY NUMBER(7,0),
    DELETED_DT DATE,
    constraint id primary key(id)
);

create table hris_classes (
    class_id number(7,0),
    class_name varchar2 (255),
    STATUS shorttext(1) NOT NULL,
    CONSTRAINT STS_CHK_hris_classes CHECK(STATUS  IN ('E','D')), 
    CREATED_BY NUMBER(7,0),
    APPROVED_BY NUMBER(7,0),
    APPROVED_DATE DATE,
    MODIFIED_BY NUMBER(7,0),
    CREATED_DT DATE,
    CHECKED_BY NUMBER(7,0),
    CHECKED_DT DATE,
    MODIFIED_DT DATE,
    DELETED_BY NUMBER(7,0),
    DELETED_DT DATE,
    constraint class_id primary key(class_id)
);

create table hris_class_travel_config (
    config_id NUMBER(7,0),
    class_id number(7,0),
    travel_type varchar2(20) NOT NULL,
    CONSTRAINT travel_type_hris_class_travel_config CHECK(travel_type  IN ('DOMESTIC','INTERNATIONAL')),
    domestic_type varchar2(7),
    CONSTRAINT domestic_type_hris_class_travel_config CHECK(domestic_type  IN ('WALKING','TRAVEL')),
    international_type varchar2(255),
    CONSTRAINT international_type_hris_class_travel_config CHECK(international_type  IN ('LISTED CITIES','OTHER INDIA CITIES','OTHER COUNTRIES')),
    rate number(7,2),
    unit varchar2(5) NOT NULL,
    CONSTRAINT unit_hris_class_travel_config CHECK(unit  IN ('KM','DAY')),
    currency varchar2(10),
    STATUS shorttext(1) NOT NULL,
    CONSTRAINT STS_CHK_hris_class_travel_config CHECK(STATUS  IN ('E','D')), 
    CREATED_BY NUMBER(7,0),
    APPROVED_BY NUMBER(7,0),
    APPROVED_DATE DATE,
    MODIFIED_BY NUMBER(7,0),
    CREATED_DT DATE,
    CHECKED_BY NUMBER(7,0),
    CHECKED_DT DATE,
    MODIFIED_DT DATE,
    DELETED_BY NUMBER(7,0),
    DELETED_DT DATE,
    constraint config_id primary key(config_id)
);

create table hris_travel_expense (
    travel_expense_id NUMBER (7,0),
    departure_DT DATE,
    departure_Place varchar2(255),
    arraival_place varchar2(255),
    arraival_DT DATE,
    travel_id NUMBER(7,0),
    config_id NUMBER(7,0),
    amount NUMBER(10,2),
    other_expense NUMBER(10,2),
    total NUMBER(10,2),
    exchange_rate NUMBER(7,2),
    expense_date DATE,
    remarks varchar2(500),
    STATUS shorttext(1) NOT NULL,
    CONSTRAINT STS_CHK_hris_travel_expense CHECK(STATUS  IN ('E','D')), 
    CREATED_BY NUMBER(7,0),
    APPROVED_BY NUMBER(7,0),
    APPROVED_DATE DATE,
    MODIFIED_BY NUMBER(7,0),
    CREATED_DT DATE,
    CHECKED_BY NUMBER(7,0),
    CHECKED_DT DATE,
    MODIFIED_DT DATE,
    DELETED_BY NUMBER(7,0),
    DELETED_DT DATE,
    constraint travel_expense_id primary key(travel_expense_id)
);

insert into hris_classes (class_id, class_name, STATUS,CREATED_DT) values (1, 'Executive Class', 'E', current_date);
insert into hris_classes (class_id, class_name, STATUS,CREATED_DT) values (2, 'First Class', 'E', current_date);
insert into hris_classes (class_id, class_name, STATUS,CREATED_DT) values (3, 'Second Class', 'E', current_date);
insert into hris_classes (class_id, class_name, STATUS,CREATED_DT) values (4, 'Third Class', 'E', current_date);
insert into hris_classes (class_id, class_name, STATUS,CREATED_DT) values (5, 'Fourth Class', 'E', current_date);

insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (1, 1, 'DOMESTIC', 'WALKING', 125, 'KM', 'E', current_date, 'NPR');
insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (2, 2, 'DOMESTIC', 'WALKING', 78.13, 'KM', 'E', current_date, 'NPR');
insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (3, 3, 'DOMESTIC', 'WALKING', 78.13, 'KM', 'E', current_date, 'NPR');
insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (4, 4, 'DOMESTIC', 'WALKING', 62.5, 'KM', 'E', current_date, 'NPR');
insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (5, 5, 'DOMESTIC', 'WALKING', 62.5, 'KM', 'E', current_date, 'NPR');

insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (6, 1, 'DOMESTIC', 'TRAVEL', 2500, 'DAY', 'E', current_date, 'NPR');
insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (7, 2, 'DOMESTIC', 'TRAVEL', 2000, 'DAY', 'E', current_date, 'NPR');
insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (8, 3, 'DOMESTIC', 'TRAVEL', 2000, 'DAY', 'E', current_date, 'NPR');
insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (9, 4, 'DOMESTIC', 'TRAVEL', 1200, 'DAY', 'E', current_date, 'NPR');
insert into hris_class_travel_config(config_id, class_id, travel_type, domestic_type, rate, unit, status, created_dt, currency) values (10, 5, 'DOMESTIC', 'TRAVEL', 1000, 'DAY', 'E', current_date, 'NPR');

insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (11, 1, 'INTERNATIONAL', 'LISTED CITIES', 255, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (12, 2, 'INTERNATIONAL', 'LISTED CITIES', 175, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (13, 3, 'INTERNATIONAL', 'LISTED CITIES', 175, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (14, 4, 'INTERNATIONAL', 'LISTED CITIES', 125, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (15, 5, 'INTERNATIONAL', 'LISTED CITIES', 100, 'DAY', 'E', current_date, 'USD');

insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (16, 1, 'INTERNATIONAL', 'OTHER INDIA CITIES', 112.5, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (17, 2, 'INTERNATIONAL', 'OTHER INDIA CITIES', 87.5, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (18, 3, 'INTERNATIONAL', 'OTHER INDIA CITIES', 87.5, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (19, 4, 'INTERNATIONAL', 'OTHER INDIA CITIES', 62.5, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (20, 5, 'INTERNATIONAL', 'OTHER INDIA CITIES', 50, 'DAY', 'E', current_date, 'USD');

insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (21, 1, 'INTERNATIONAL', 'OTHER COUNTRIES', 225, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (22, 2, 'INTERNATIONAL', 'OTHER COUNTRIES', 175, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (23, 3, 'INTERNATIONAL', 'OTHER COUNTRIES', 175, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (24, 4, 'INTERNATIONAL', 'OTHER COUNTRIES', 125, 'DAY', 'E', current_date, 'USD');
insert into hris_class_travel_config(config_id, class_id, travel_type, international_type, rate, unit, status, created_dt, currency) values (25, 5, 'INTERNATIONAL', 'OTHER COUNTRIES', 100, 'DAY', 'E', current_date, 'USD');


---edit in hana database for WORKFORCE-----
create table hris_service_group as (select * from hris_rec_service_events_types);

ALTER TABLE hris_employees ADD (service_group_id NUMBER(4));

CREATE TABLE HRIS_WORKFORCE
   (	
    WORKFORCE_ID NUMBER(7),
	COMPANY_ID NUMBER(7), 
	LOCATION_ID NUMBER(7) NOT NULL, 
	DEPARTMENT_ID NUMBER(7),
	POSITION_ID NUMBER(7) NOT NULL, 
	service_group_ID NUMBER(7), 
	service_type_ID NUMBER(7),
	DESIGNATION_ID NUMBER(7), 
	QUOTA NUMBER(5) NOT NULL, 
	REMARKS VARCHAR(2000) DEFAULT NULL, 
	CREATED_BY NUMBER(7), 
	CREATED_DT DATE NOT NULL,
    MODIFIED_BY NUMBER(7),
	MODIFIED_DT DATE,
	CHECKED_BY NUMBER(7) DEFAULT NULL,
	CHECKED_DT DATE,
	APPROVED_BY NUMBER(7) DEFAULT NULL,
	APPROVED_DT DATE,
	STATUS SHORTTEXT(1) DEFAULT 'E' NOT NULL ,
	constraint pk_hris_workforce  PRIMARY KEY (WORKFORCE_ID),
	constraint HRIS_WORKFORCE_status CHECK (STATUS IN ('E','D')),
	CONSTRAINT UNQ_WORKFORCE UNIQUE (COMPANY_ID, LOCATION_ID,POSITION_ID),
	CONSTRAINT FK_WORKFORCE1 FOREIGN KEY (COMPANY_ID) REFERENCES HRIS_COMPANY(COMPANY_ID),
	CONSTRAINT FK_WORKFORCE3 FOREIGN KEY (POSITION_ID) REFERENCES HRIS_POSITIONS(POSITION_ID),
	CONSTRAINT FK_WORKFORCEA FOREIGN KEY (CREATED_BY) REFERENCES HRIS_EMPLOYEES(EMPLOYEE_ID),
	CONSTRAINT FK_WORKFORCEM FOREIGN KEY (MODIFIED_BY) REFERENCES HRIS_EMPLOYEES(EMPLOYEE_ID),
	CONSTRAINT FK_WORKFORCEC FOREIGN KEY (CHECKED_BY) REFERENCES HRIS_EMPLOYEES(EMPLOYEE_ID),
	CONSTRAINT FK_WORKFORCED FOREIGN KEY (APPROVED_BY) REFERENCES HRIS_EMPLOYEES(EMPLOYEE_ID)
   );
alter table hris_functional_levels add (roles varchar(5000));
alter table hris_positions add (roles varchar(5000));
alter table hris_job_history add (roles varchar(5000));

DROP TABLE HRIS_GL_ENTRY;
DROP TABLE HRIS_FINANCE_DATA;

create table HRIS_FINANCE_DATA (
    FINANCE_DATA_ID NUMBER(7) PRIMARY KEY,
    MODULE_CODE varchar(2) NOT NULL,
    MASTER_ID NUMBER(7),
    REQUEST_ID NUMBER(7) NOT NULL,
    AMOUNT NUMBER(12,2),
    BRANCH_ID NUMBER(7),
    OFFICE_ID NUMBER(7),
    department_id number(7)
);

create table HRIS_GL_ENTRY (
   GL_ENTRY_ID NUMBER(10) not null,
   FINANCE_DATA_ID NUMBER(10),
   --FOREIGN KEY (FINANCE_DATA_ID) REFERENCES HRIS_FINANCE_DATA(FINANCE_DATA_ID),
   EMPLOYEE_ID NUMBER(7) not null,
   EMPLOYEE_CODE VARCHAR(255),
   VOUCHER_NO VARCHAR(30), --MODULE_CODE/MASTER_ID/BRANCH_ID/OFFICE_ID/LOCATION_ID/LOT_ID
   CREATED_DATE DATE not null,
   CREATED_BY NUMBER(7) not null,
   AMOUNT NUMBER(12,2),
   DR_ACC_Code VARCHAR(10),
   CR_ACC_Code VARCHAR(10),
   BRANCH_ID NUMBER(7),
   CC1 NUMBER(7),
   CC2 NUMBER(7),
   CC3 VARCHAR(2) default 'NA',
   CC4 VARCHAR(2) default 'NA',
   SYNC VARCHAR(1) default 'N',
   SYNCED_DATE DATE
);


alter table HRIS_LOAN_MASTER_SETUP add(
 market_interest_rate float,
 DR_ACC_Code VARCHAR(10),
 CR_ACC_Code VARCHAR(10)
);

alter table HRIS_EMPLOYEE_LOAN_REQUEST add(
 sync  VARCHAR(1)
);

CREATE TABLE HRIS_PAY_SETUP_SPECIAL(
    PAY_ID INT,
    SALARY_TYPE_ID INT,
    FORMULA varchar(5000),
    STATUS CHAR(1) NOT NULL ,
    FLAG CHAR(1) ,
    CREATED_DT DATE DEFAULT CURRENT_DATE,
    CREATED_BY NUMBER(7,0),
    MODIFIED_BY NUMBER(7,0),
    MODIFIED_DT DATE,
    CONSTRAINT SETUP_SPECIAL_STATUS_CHECK CHECK (STATUS IN('D','E')),
    CONSTRAINT SETUP_SPECIAL_FLAG_CHECK CHECK (FLAG IN ('Y', 'N'))
   );

ALTER TABLE HRIS_PAY_SETUP_SPECIAL ADD PRIMARY KEY (PAY_ID, SALARY_TYPE_ID);

ALTER TABLE HRIS_PAY_SETUP ADD 
PRIMARY KEY (PAY_ID);
  
ALTER TABLE HRIS_PAY_SETUP_SPECIAL ADD 
FOREIGN KEY (PAY_ID) REFERENCES HRIS_PAY_SETUP(PAY_ID);

ALTER TABLE HRIS_SALARY_TYPE ADD 
PRIMARY KEY (SALARY_TYPE_ID);


 ALTER TABLE HRIS_SALARY_SHEET_GROUP ADD PRIMARY KEY (GROUP_ID);
  
ALTER TABLE HRIS_PAY_SETUP_SPECIAL ADD 
FOREIGN KEY (SALARY_TYPE_ID) REFERENCES HRIS_SALARY_TYPE(SALARY_TYPE_ID);

ALTER TABLE hris_ss_pay_value_modified ADD (remarks varchar(600));

create table hris_employee_grade_info(
  employee_id number,
  opening_grade number,
  ADDITIONAL_GRADE number,
  GRADE_VALUE number,
  GRADE_DATE date,
  FISCAL_YEAR_ID number,
  REMARKS varchar(500),
  created_by number,
  created_date date,
  modified_by number,
  modified_date date
  
);

ALTER TABLE hris_employee_grade_info ADD PRIMARY KEY (employee_id, FISCAL_YEAR_ID);
ALTER TABLE hris_employee_grade_info ADD FOREIGN KEY (employee_id) REFERENCES hris_employees(employee_id);
ALTER TABLE HRIS_FISCAL_YEARS ADD PRIMARY KEY ( FISCAL_YEAR_ID);


ALTER TABLE hris_employee_grade_info ADD FOREIGN KEY (FISCAL_YEAR_ID) REFERENCES HRIS_FISCAL_YEARS(FISCAL_YEAR_ID);

CREATE TABLE HRIS_EMPLOYEE_FILE_SETUP
(	FILE_ID NUMBER(7,0),
     FILE_NAME VARCHAR2(255),
     STATUS CHAR(1),
	FILE_TYPE_CODE CHAR(3),
	CREATED_DT DATE,
	CREATED_BY NUMBER(7,0),
	MODIFIED_DT DATE,
	MODIFIED_BY NUMBER(7,0)
   );

RENAME COLUMN HRIS_EMPLOYEE_FILE.FILE_NAME TO FILE_ID;