ALTER TABLE HRIS_ATTENDANCE_DETAIL
DROP COLUMN LATE_STATUS;
-- 
ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD LATE_STATUS CHAR(1 BYTE) CHECK (LATE_STATUS IN ('L','E','B','N','X','Y'));

ALTER TABLE HRIS_ATTENDANCE ADD REMARKS VARCHAR(255 BYTE);

ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD ALLOW_GRACE_LEAVE CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(ALLOW_GRACE_LEAVE IN ('Y','N'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IS_MONTHLY CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(IS_MONTHLY IN ('Y','N'));

ALTER TABLE HRIS_EMPLOYEE_LEAVE_REQUEST ADD GRACE_PERIOD CHAR(1 BYTE) DEFAULT NULL CHECK(GRACE_PERIOD IN ('E','L'));

ALTER TABLE HRIS_TASK
DROP CONSTRAINT FK_TASK_BRA_BRA_ID;
ALTER TABLE HRIS_TASK
DROP CONSTRAINT FK_TASK_COMP_COMP_ID;

ALTER TABLE HRIS_FISCAL_YEARS ADD FISCAL_YEAR_NAME VARCHAR2(10 BYTE);

DECLARE
  FISCAL_YEAR_ID NUMBER;
  START_DATE     DATE;
  END_DATE       DATE;
  CURSOR YEARS
  IS
    SELECT FISCAL_YEAR_ID,START_DATE,END_DATE FROM HRIS_FISCAL_YEARS;
BEGIN
  OPEN YEARS;
  LOOP
    FETCH YEARS INTO FISCAL_YEAR_ID,START_DATE,END_DATE;
    EXIT
  WHEN YEARS%NOTFOUND;
    UPDATE HRIS_FISCAL_YEARS
    SET FISCAL_YEAR_NAME = CONCAT(TO_CHAR(START_DATE,'YYYY')||'/',TO_CHAR(END_DATE,'YYYY'));
  END LOOP;
  CLOSE YEARS;
END;

ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IS_SUBSTITUTE_MANDATORY CHAR(1 BYTE) DEFAULT 'Y'NOT NULL CHECK (IS_SUBSTITUTE_MANDATORY IN ('Y','N'));


ALTER TABLE HRIS_EMPLOYEES
ADD IS_CEO CHAR (1 BYTE) CHECK (IS_CEO IN ('Y','N'));

ALTER TABLE HRIS_EMPLOYEES
ADD IS_HR CHAR (1 BYTE) CHECK (IS_HR IN ('Y','N'));

ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD ASSIGN_ON_EMPLOYEE_SETUP CHAR(1 BYTE) DEFAULT 'Y'NOT NULL CHECK (ASSIGN_ON_EMPLOYEE_SETUP IN ('Y','N'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IS_PRODATA_BASIS CHAR(1 BYTE) DEFAULT 'Y'NOT NULL CHECK (IS_PRODATA_BASIS IN ('Y','N'));




-- to isnert new fiscal year

INSERT INTO HRIS_FISCAL_YEARS (FISCAL_YEAR_ID,START_DATE,END_DATE,CREATED_DT,STATUS,FISCAL_YEAR_NAME)
VALUES (3,TO_DATE('16-07-2017','DD-MM-YYYY'),TO_DATE('16-07-2018','DD-MM-YYYY'),TRUNC(SYSDATE),'E','2017/2018');

-- TO INSERT NEW MONTH CODE

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,25,'Shrawan','Shrawan',TO_DATE('16-07-2017','DD-MM-YYYY'),TO_DATE('16-08-2017','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,26,'Bhadra','Bhadra',TO_DATE('17-08-2017','DD-MM-YYYY'),TO_DATE('16-09-2017','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,27,'Ashwin','Ashwin',TO_DATE('17-09-2017','DD-MM-YYYY'),TO_DATE('17-10-2017','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,28,'Kartik','Kartik',TO_DATE('18-10-2017','DD-MM-YYYY'),TO_DATE('16-11-2017','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,29,'Mangsir','Mangsir',TO_DATE('17-11-2017','DD-MM-YYYY'),TO_DATE('15-12-2017','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,30,'Paush','Paush',TO_DATE('16-12-2017','DD-MM-YYYY'),TO_DATE('14-01-2018','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,31,'Magh','Magh',TO_DATE('15-01-2018','DD-MM-YYYY'),TO_DATE('12-02-2018','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,32,'Falgun','Falgun',TO_DATE('13-02-2018','DD-MM-YYYY'),TO_DATE('14-03-2018','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,33,'Chaitra','Chaitra',TO_DATE('15-03-2018','DD-MM-YYYY'),TO_DATE('13-04-2018','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,34,'Baishakh','Baishakh',TO_DATE('14-04-2018','DD-MM-YYYY'),TO_DATE('14-05-2018','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,35,'Jestha','Jestha',TO_DATE('15-05-2018','DD-MM-YYYY'),TO_DATE('14-06-2018','DD-MM-YYYY'),TRUNC(SYSDATE),'E');

INSERT INTO HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,CREATED_DT,STATUS)
VALUES (3,36,'Ashadh','Ashadh',TO_DATE('15-06-2018','DD-MM-YYYY'),TO_DATE('16-07-2018','DD-MM-YYYY'),TRUNC(SYSDATE),'E');


ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP 
ADD ASSIGN_ON_EMPLOYEE_SETUP CHAR(1 BYTE) DEFAULT 'Y'NOT NULL CHECK (ASSIGN_ON_EMPLOYEE_SETUP IN ('Y','N'));


ALTER TABLE HRIS_TRAINING_MASTER_SETUP ADD IS_WITHIN_COMPANY CHAR( 1 BYTE) DEFAULT 'Y' NOT NULL CHECK (IS_WITHIN_COMPANY IN ('Y','N'));

ALTER TABLE HRIS_APPRAISAL_QUESTION
ADD (HR_FLAG CHAR(1 BYTE) DEFAULT 'N' CHECK (HR_FLAG IN ('Y','N')),
    HR_RATING CHAR(1 BYTE) DEFAULT 'N' CHECK (HR_RATING IN ('Y','N')));

ALTER TABLE HRIS_EMPLOYEES ADD ADDR_PERM_DISTRICT_ID NUMBER(7,0) ;
ALTER TABLE HRIS_EMPLOYEES ADD CONSTRAINT FK_EMP_DISTRICT_ID_1 FOREIGN KEY(ADDR_PERM_DISTRICT_ID) REFERENCES HRIS_DISTRICTS(DISTRICT_ID);
ALTER TABLE HRIS_EMPLOYEES ADD ADDR_PERM_ZONE_ID NUMBER(7,0);
ALTER TABLE HRIS_EMPLOYEES ADD CONSTRAINT FK_EMP_ZONE_ID FOREIGN KEY(ADDR_PERM_ZONE_ID) REFERENCES HRIS_ZONES(ZONE_ID);
ALTER TABLE HRIS_EMPLOYEES ADD ADDR_TEMP_DISTRICT_ID NUMBER(7,0);
ALTER TABLE HRIS_EMPLOYEES ADD CONSTRAINT FK_EMP_DISTRICT_ID_2 FOREIGN KEY (ADDR_TEMP_DISTRICT_ID) REFERENCES HRIS_DISTRICTS(DISTRICT_ID);
ALTER TABLE HRIS_EMPLOYEES ADD ADDR_TEMP_ZONE_ID NUMBER(7,0);
ALTER TABLE HRIS_EMPLOYEES ADD CONSTRAINT FK_EMP_ZONE_ID_2 FOREIGN KEY (ADDR_TEMP_ZONE_ID) REFERENCES HRIS_ZONES(ZONE_ID);


ALTER TABLE HRIS_EMPLOYEES ADD PERMANENT_DATE DATE;

ALTER TABLE HRIS_FLAT_VALUE_DETAIL DROP COLUMN MONTH_ID;

ALTER TABLE HRIS_JOB_HISTORY ADD FROM_SALARY NUMBER(11,2);

ALTER TABLE HRIS_JOB_HISTORY ADD TO_SALARY NUMBER(11,2);


CREATE TABLE HRIS_BIRTHDAY_MESSAGES
   (	BIRTHDAY_ID NUMBER(7,0) PRIMARY KEY, 
	BIRTHDAY_DATE DATE NOT NULL, 
	FROM_EMPLOYEE NUMBER(7,0) NOT NULL, 
	TO_EMPLOYEE NUMBER(7,0) NOT NULL, 
	MESSAGE VARCHAR2(2000 BYTE) NOT NULL, 
	CREATED_DT DATE NOT NULL, 
	STATUS CHAR(1 BYTE) DEFAULT 'E' NOT NULL CHECK (STATUS IN ('E','D')), 
	MODIFIED_DT DATE
   ) ;
ALTER TABLE HRIS_BIRTHDAY_MESSAGES ADD CONSTRAINT FK_BIRTHDAY_MESS_EMP_ID_1 FOREIGN KEY (FROM_EMPLOYEE)
	  REFERENCES HRIS_EMPLOYEES (EMPLOYEE_ID);
ALTER TABLE HRIS_BIRTHDAY_MESSAGES ADD CONSTRAINT FK_BIRTHDAY_MESS_EMP_ID_2 FOREIGN KEY (TO_EMPLOYEE)
	  REFERENCES HRIS_EMPLOYEES (EMPLOYEE_ID);


ALTER TABLE HRIS_NEWS MODIFY (NEWS_LDESC VARCHAR2(3000));


ALTER TABLE HRIS_USERS
MODIFY PASSWORD VARCHAR2(64);

ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD HALFDAY_FLAG CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(HALFDAY_FLAG IN ('Y','N'));

ALTER TABLE HRIS_USERS
ADD IS_LOCKED CHAR(1 BYTE) DEFAULT ('N') NOT NULL CHECK(IS_LOCKED IN ('Y','N'));


ALTER TABLE HRIS_EMPLOYEE_TRAVEL_REQUEST MODIFY( REQUESTED_AMOUNT NULL); 
CREATE TABLE HRIS_PREFERENCES
  (
    KEY   VARCHAR2(100 BYTE) PRIMARY KEY,
    VALUE VARCHAR2(100 BYTE)
  );
--
SELECT *
FROM
  (SELECT * FROM HRIS_PREFERENCES
  ) PIVOT ( MAX(VALUE) FOR KEY IN (10,20,30,40) );

ALTER TABLE HRIS_EMPLOYEE_SHIFT_ASSIGN ADD START_DATE DATE DEFAULT TO_DATE('16-JUL-2017','DD-MON-YYYY') NOT NULL;
--
ALTER TABLE HRIS_EMPLOYEE_SHIFT_ASSIGN ADD END_DATE DATE ;
--
ALTER TABLE HRIS_EMPLOYEE_SHIFT_ASSIGN ADD ID NUMBER(7,0);

ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD ENABLE_SUBSTITUTE CHAR(1 BYTE) DEFAULT 'Y' NOT NULL CHECK(ENABLE_SUBSTITUTE IN ('Y','N'));
-- 

ALTER TABLE HRIS_EMPLOYEES
DROP CONSTRAINT EMP_EMAIL_OFF_UN;
--
ALTER TABLE HRIS_EMPLOYEES
DROP CONSTRAINT EMP_EMAIL_PER_UN;


ALTER TABLE HRIS_JOB_HISTORY ADD RETIRED_FLAG CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(RETIRED_FLAG IN ('Y','N'));
--
ALTER TABLE HRIS_JOB_HISTORY ADD DISABLED_FLAG CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(DISABLED_FLAG IN ('Y','N'));
-- 
ALTER TABLE HRIS_JOB_HISTORY ADD EVENT_DATE DATE DEFAULT SYSDATE NOT NULL;

ALTER TABLE HRIS_ROLES ADD CONTROL CHAR(1 BYTE) DEFAULT 'F' NOT NULL CHECK(CONTROL IN ('F','C','U'));

ALTER TABLE HRIS_ROLES ADD ALLOW_ADD CHAR(1 BYTE) DEFAULT 'Y' NOT NULL CHECK(ALLOW_ADD IN ('Y','N'));

ALTER TABLE HRIS_ROLES ADD ALLOW_UPDATE CHAR(1 BYTE) DEFAULT 'Y' NOT NULL CHECK(ALLOW_UPDATE IN ('Y','N'));

ALTER TABLE HRIS_ROLES ADD ALLOW_DELETE CHAR(1 BYTE) DEFAULT 'Y' NOT NULL CHECK(ALLOW_DELETE IN ('Y','N'));

  CREATE TABLE "HRIS_EMPLOYEE_PENALTY_DAYS" 
   (	"EMPLOYEE_ID" NUMBER(7,0), 
	"ATTENDANCE_DT" DATE
   )

CREATE TABLE HRIS_EMPLOYEE_SHIFT_ROASTER
  (
    EMPLOYEE_ID NUMBER(7,0),
    SHIFT_ID    NUMBER(7,0),
    FOR_DATE    DATE NOT NULL,
    CREATED_BY  NUMBER(7,0),
    MODIFIED_BY NUMBER(7,0),
    CREATED_DT  NUMBER(7,0),
    MODIFED_DT  NUMBER(7,0),
    CONSTRAINT FK_ROASTER_EMPLOYEE_EMP_ID FOREIGN KEY(EMPLOYEE_ID) REFERENCES HRIS_EMPLOYEES(EMPLOYEE_ID),
    CONSTRAINT FK_ROASTER_SHIFT_SHIFT_ID FOREIGN KEY(SHIFT_ID) REFERENCES HRIS_SHIFTS(SHIFT_ID)
  );


-- HRIS_PRELOAD_ATTENANCE PROCEDURE IS CHANGED
ALTER TABLE HRIS_USERS ADD IS_LOCKED CHAR(1 BYTE) DEFAULT ('N') NOT NULL;

ALTER TABLE HRIS_ATTENDANCE_REQUEST
MODIFY IN_TIME NULL;

ALTER TABLE HRIS_ATTENDANCE_REQUEST
MODIFY OUT_TIME NULL;


-- HRIS_BACKDATE_ATTENDANCE PROCEDURE IS CHANGED




-- HRIS_PRELOAD_ATTENDANCE PROCEDURE IS CHANGED

CREATE TABLE HRIS_EMPLOYEE_SHIFTS
  (
    EMPLOYEE_ID NUMBER(7,0),
    SHIFT_ID    NUMBER(7,0),
    START_DATE  DATE,
    END_DATE    DATE
  );

-- HRIS_PRELOAD_ATTENDANCE PROCEDURE IS CHANGED
-- HRIS_REATTENDANCE PROCEDURE IS CHANGED
-- DEVICE_ATTENDANCE_TRIGGER IS CHANGED


CREATE TABLE HRIS_EMPLOYEE_LEAVE_ADDITION
  (
    EMPLOYEE_ID NUMBER(7,0),
    LEAVE_ID    NUMBER(7,0),
    NO_OF_DAYS FLOAT,
    REMARKS      VARCHAR2(512 BYTE),
    CREATED_DATE DATE,
    WOD_ID NUMBER(7,0),
    WOH_ID NUMBER(7,0)
  );

-- HRIS_AFTER_LEAVE_ADDITION TRIGGER IS ADDED


DROP TABLE HRIS_EMPLOYEE_PENALTY_DAYS;
--
CREATE TABLE HRIS_EMPLOYEE_PENALTY_DAYS
  (
    EMPLOYEE_ID   NUMBER(7,0),
    ATTENDANCE_DT DATE,
    LEAVE_ID      NUMBER(7,0),
    NO_OF_DAYS FLOAT,
    REMARKS      VARCHAR2(512 BYTE),
    CREATED_DATE DATE
  );

ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD GRACE_PERIOD   CHAR(1 BYTE) DEFAULT NULL CHECK(GRACE_PERIOD   IN ('E','L'));
ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD HALFDAY_PERIOD CHAR(1 BYTE) DEFAULT NULL CHECK(HALFDAY_PERIOD IN ('F','S'));

-- HRIS_PRELOAD_ATTENDANCE CHANGED
-- HRIS_POST_ATTENDANCE_CHECK
-- HRIS_REATTENDANCE
-- DEVICE _ATTENDANCE_TRIGGER


ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD DEDUCTION_PRIORITY_NO NUMBER(7,0);


-- HRIS_AFTER_EMPLOYEE_PENALTY
-- HRIS_AFTER_LEAVE_ADDITION


ALTER TABLE HRIS_MONTH_CODE ADD YEAR NUMBER DEFAULT 2073 NOT NULL ;
ALTER TABLE HRIS_MONTH_CODE ADD MONTH_NO NUMBER DEFAULT 1 NOT NULL;


CREATE TABLE HRIS_PENALIZED_MONTHS
  (
    YEAR         NUMBER NOT NULL,
    MONTH_NO     NUMBER NOT NULL,
    NO_OF_DAYS   NUMBER NOT NULL,
    CREATED_DATE DATE,
    CREATED_BY NUMBER(7,0),
    MODIFIED_DATE DATE,
    MODIFIED_BY NUMBER(7,0)
  );

-- HRIS_LATE_LEAVE_DEDUCTION


UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=1 WHERE MONTH_ID = 22;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=2 WHERE MONTH_ID = 23;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=3 WHERE MONTH_ID = 24;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=4 WHERE MONTH_ID = 25;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=5 WHERE MONTH_ID = 26;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=6 WHERE MONTH_ID = 27;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=7 WHERE MONTH_ID = 28;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=8 WHERE MONTH_ID = 29;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=9 WHERE MONTH_ID = 30;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=10 WHERE MONTH_ID = 31;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=11 WHERE MONTH_ID = 32;
UPDATE HRIS_MONTH_CODE SET YEAR = 2074,MONTH_NO=12 WHERE MONTH_ID = 33;
UPDATE HRIS_MONTH_CODE SET YEAR = 2075,MONTH_NO=1 WHERE MONTH_ID = 34;
UPDATE HRIS_MONTH_CODE SET YEAR = 2075,MONTH_NO=2 WHERE MONTH_ID = 35;
UPDATE HRIS_MONTH_CODE SET YEAR = 2075,MONTH_NO=3 WHERE MONTH_ID = 36;

-- DEVICE ATTENDANCE TRIGGER

-- HRIS_REATTENDANCE
-- HRIS_WOD_LEAVE_ADDITION
-- HRIS_WOD_OT_ADDITION
-- HRIS_WOH_LEAVE_ADDITION
-- HRIS_WOH_OT_ADDITION


ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD WOD_ID NUMBER(7,0);
ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD WOH_ID NUMBER(7,0);

-- HRIS_PRELOAD_ATTENDANCE
-- HRIS_REATTENDANCE
-- DEVICE_ATTENDANCE_TRIGGER

ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD TWO_DAY_SHIFT CHAR(1 BYTE) DEFAULT 'D' NOT NULL CHECK(TWO_DAY_SHIFT IN ('E','D'));
-- HRIS_PRELOAD_ATTENDANCE
-- DEVICE_ATTENDANCE_TRIGGER
-- HRIS_REATTENDANCE
-- HRIS_REATTENDANCE_TWO_DAY


-- HRIS_RECALCULATE_LEAVE
-- HRIS_WOD_LEAVE_ADDITION
-- HRIS_WOD_OT_ADDITION
-- HRIS_WOH_LEAVE_ADDITION
-- HRIS_WOH_OT_ADDITION
-- HRIS_MENU_ROLE_ASSIGN ADDED

CREATE TABLE HRIS_POSITION_MONTHLY_VALUE
  (
    MTH_ID         NUMBER(7,0),
    POSITION_ID    NUMBER(7,0),
    MONTH_ID       NUMBER(7,0),
    ASSIGNED_VALUE NUMBER
  );
--
ALTER TABLE HRIS_MONTHLY_VALUE_SETUP
DROP COLUMN COMPANY_ID;
--
ALTER TABLE HRIS_MONTHLY_VALUE_SETUP
DROP COLUMN BRANCH_ID;
--
ALTER TABLE HRIS_MONTHLY_VALUE_SETUP
DROP COLUMN SHOW_AT_RULE;
--
ALTER TABLE HRIS_MONTHLY_VALUE_SETUP
DROP COLUMN SH_INDEX_NO;
--
ALTER TABLE HRIS_MONTHLY_VALUE_SETUP
ADD CREATED_BY NUMBER(7,0);
--
ALTER TABLE HRIS_MONTHLY_VALUE_SETUP
ADD MODIFIED_BY NUMBER(7,0);
--
ALTER TABLE HRIS_MONTHLY_VALUE_SETUP MODIFY (MTH_LDESC NULL);
-- 
ALTER TABLE HRIS_EMPLOYEE_LEAVE_REQUEST MODIFY( REMARKS VARCHAR2(255 ));

CREATE TABLE HRIS_HOLIDAY_ASSIGN
  (
    HOLIDAY_ID            NUMBER(7,0) NOT NULL,
    COMPANY_ID            NUMBER(7,0),
    BRANCH_ID             NUMBER(7,0),
    DEPARTMENT_ID         NUMBER(7,0),
    DESIGNATION_ID        NUMBER(7,0),
    POSITION_ID           NUMBER(7,0),
    SERVICE_TYPE_ID       NUMBER(7,0),
    SERVICE_EVENT_TYPE_ID NUMBER(7,0),
    EMPLOYEE_TYPE         CHAR(1 BYTE),
    GENDER_ID             NUMBER(7,0),
    EMPLOYEE_ID           NUMBER(7,0)
  );

-- HRIS_HOLIDAY_ASSIGN_AUTO PROCEDURE CREATED

CREATE TABLE HRIS_NEWS_TYPE
  (
    NEWS_TYPE_ID   NUMBER(7,0) PRIMARY KEY,
    NEWS_TYPE_DESC VARCHAR2(255) NOT NULL,
    UPLOAD_FLAG    CHAR(1 BYTE) DEFAULT 'Y' CHECK (UPLOAD_FLAG IN ('Y','N')),
    STATUS         CHAR(1 BYTE) DEFAULT 'E' CHECK(STATUS       IN ('E','D')),
    CREATED_DT     DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
    CREATED_BY     NUMBER(7,0),
    MODIFIED_DT    DATE,
    MODIFIED_BY    NUMBER(7,0)
  );

-- drop TWO constaInt in hris news for news_type ONLY

ALTER TABLE HRIS_NEWS DROP COLUMN NEWS_TYPE;
ALTER TABLE HRIS_NEWS ADD NEWS_TYPE NUMBER(7,0);


CREATE TABLE HRIS_NEWS_FILE
  (
    NEWS_FILE_ID NUMBER(7,0) PRIMARY KEY,
    NEWS_ID      NUMBER(7,0) NOT NULL,
    FILE_PATH    VARCHAR2(255),
    STATUS       CHAR(1 BYTE) DEFAULT 'E' CHECK(STATUS IN ('E','D')),
    CREATED_DT   DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
    CREATED_BY   NUMBER(7,0),
    MODIFIED_DT  DATE,
    MODIFIED_BY  NUMBER(7,0),
    REMARKS      VARCHAR2(255),
    FILE_NAME    VARCHAR2(255)
  );
ALTER TABLE HRIS_NEWS ADD NEWS_EXPIRY_DT DATE DEFAULT TRUNC(SYSDATE) NOT NULL;

CREATE TABLE HRIS_NEWS_TO
  (
    NEWS_ID               NUMBER(7,0),
    COMPANY_ID            NUMBER(7,0),
    BRANCH_ID             NUMBER(7,0),
    DEPARTMENT_ID         NUMBER(7,0),
    DESIGNATION_ID        NUMBER(7,0),
    POSITION_ID           NUMBER(7,0),
    SERVICE_TYPE_ID       NUMBER(7,0),
    SERVICE_EVENT_TYPE_ID NUMBER(7,0),
    EMPLOYEE_TYPE         CHAR(1 BYTE),
    GENDER_ID             NUMBER(7,0),
    EMPLOYEE_ID           NUMBER(7,0)
  );

ALTER TABLE HRIS_NEWS DROP COLUMN COMPANY_ID;

ALTER TABLE HRIS_NEWS DROP COLUMN BRANCH_ID;
ALTER TABLE HRIS_NEWS DROP COLUMN DEPARTMENT_ID;
ALTER TABLE HRIS_NEWS DROP COLUMN DESIGNATION_ID;

CREATE TABLE HRIS_NEWS_EMPLOYEE
  (NEWS_ID NUMBER(7,0),EMPLOYEE_ID NUMBER(7,0)
  );



ALTER TABLE HRIS_MONTH_CODE ADD FISCAL_YEAR_MONTH_NO NUMBER(2,0);

ALTER TABLE HRIS_COMPULSORY_OVERTIME ADD COMPULSORY_OT_DESC VARCHAR2(255 BYTE);

ALTER TABLE HRIS_SHIFTS ADD HALF_DAY_IN_TIME TIMESTAMP;

ALTER TABLE HRIS_SHIFTS ADD HALF_DAY_OUT_TIME TIMESTAMP;

ALTER TABLE HRIS_LEAVE_MASTER_SETUP DROP COLUMN COMPANY_ID ;
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COMPANY_ID VARCHAR2(4000 BYTE);

ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD BRANCH_ID       VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD DEPARTMENT_ID   VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD DESIGNATION_ID  VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD POSITION_ID     VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD SERVICE_TYPE_ID VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD EMPLOYEE_TYPE   VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD GENDER_ID       VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD EMPLOYEE_ID     VARCHAR2(4000 BYTE);




ALTER TABLE HRIS_COMPANY ADD LINK_TRAVEL_TO_SYNERGY CHAR(1 BYTE) CHECK(LINK_TRAVEL_TO_SYNERGY IN ('Y','N'));
ALTER TABLE HRIS_COMPANY ADD DR_ACC_CODE            VARCHAR2(255 BYTE);
ALTER TABLE HRIS_COMPANY ADD CR_ACC_CODE            VARCHAR2(255 BYTE);
ALTER TABLE HRIS_COMPANY ADD EXCESS_CR_ACC_CODE     VARCHAR2(255 BYTE);
ALTER TABLE HRIS_COMPANY ADD LESS_DR_ACC_CODE       VARCHAR2(255 BYTE);
ALTER TABLE HRIS_COMPANY ADD FORM_CODE              VARCHAR2(255 BYTE);
ALTER TABLE HRIS_COMPANY ADD EQUAL_CR_ACC_CODE VARCHAR2(255 BYTE);

ALTER TABLE HRIS_ROLES MODIFY (CONTROL VARCHAR2(2 BYTE));


CREATE TABLE HRIS_JOBS
  (
    JOB_ID      NUMBER(7,0) PRIMARY KEY,
    WHAT        VARCHAR2(4000 BYTE),
    EXECUTED    CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(EXECUTED IN ('Y','N')),
    EXECUTED_AT DATE,
    STATUS      CHAR(1 BYTE) CHECK(STATUS IN ('S','F')),
    CAUSE       VARCHAR2(4000 BYTE)
  );

ALTER TABLE HRIS_EMPLOYEE_TRAINING_REQUEST ADD IS_WITHIN_COMPANY CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(IS_WITHIN_COMPANY IN ('Y','N'));



ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD TRAINING_TYPE CHAR(1 BYTE) CHECK(TRAINING_TYPE IN ('R','A'));

CREATE TABLE HRIS_LOCATIONS
  (
    LOCATION_ID        NUMBER(7,0) PRIMARY KEY,
    LOCATION_CODE      VARCHAR2(15 BYTE),
    LOCATION_EDESC     VARCHAR2(255 BYTE) NOT NULL,
    LOCATION_LDESC     VARCHAR2(255 BYTE),
    PARENT_LOCATION_ID NUMBER(7,0),
    CREATED_DT         DATE,
    CREATED_BY         NUMBER(7,0),
    MODIFIED_DT        DATE,
    MODIFIED_BY        NUMBER(7,0),
    DELETED_DT         DATE,
    DELETED_BY         NUMBER(7,0),
    STATUS             CHAR(1 BYTE) DEFAULT 'E' NOT NULL CHECK(STATUS IN ('E','D'))
  );
CREATE TABLE HRIS_FUNCTIONAL_TYPES
  (
    FUNCTIONAL_TYPE_ID    NUMBER(7,0) PRIMARY KEY,
    FUNCTIONAL_TYPE_CODE  VARCHAR2(15 BYTE) ,
    FUNCTIONAL_TYPE_EDESC VARCHAR2(255 BYTE) NOT NULL,
    FUNCTIONAL_TYPE_LDESC VARCHAR2(255 BYTE),
    CREATED_DT            DATE,
    CREATED_BY            NUMBER(7,0),
    MODIFIED_DT           DATE,
    MODIFIED_BY           NUMBER(7,0),
    DELETED_DT            DATE,
    DELETED_BY            NUMBER(7,0),
    STATUS                CHAR(1 BYTE) DEFAULT 'E' NOT NULL CHECK(STATUS IN ('E','D'))
  );
CREATE TABLE HRIS_FUNCTIONAL_LEVELS
  (
    FUNCTIONAL_LEVEL_ID    NUMBER(7,0) PRIMARY KEY,
    FUNCTIONAL_LEVEL_NO    VARCHAR2(15 BYTE) NOT NULL,
    FUNCTIONAL_LEVEL_EDESC VARCHAR2(255 BYTE) NOT NULL,
    FUNCTIONAL_LEVEL_LDESC VARCHAR2(255 BYTE),
    FUNCTIONAL_TYPE_ID     NUMBER(7,0),
    CREATED_DT             DATE,
    CREATED_BY             NUMBER(7,0),
    MODIFIED_DT            DATE,
    MODIFIED_BY            NUMBER(7,0),
    DELETED_DT             DATE,
    DELETED_BY             NUMBER(7,0),
    STATUS                 CHAR(1 BYTE) DEFAULT 'E' NOT NULL CHECK(STATUS IN ('E','D'))
  );


ALTER TABLE HRIS_EMPLOYEES ADD LOCATION_ID         NUMBER(7,0);
ALTER TABLE HRIS_EMPLOYEES ADD FUNCTIONAL_TYPE_ID  NUMBER(7,0);
ALTER TABLE HRIS_EMPLOYEES ADD FUNCTIONAL_LEVEL_ID NUMBER(7,0);

ALTER TABLE HRIS_EMPLOYEES ADD RESIGNED_FLAG CHAR(1 BYTE) DEFAULT 'N' CHECK(RESIGNED_FLAG IN ('Y','N'));
ALTER TABLE HRIS_EMPLOYEES ADD RESIGNED_DATE DATE;
--
ALTER TABLE HRIS_EMPLOYEES ADD PERMANENT_FLAG CHAR(1 BYTE) DEFAULT 'N' CHECK(PERMANENT_FLAG IN ('Y','N'));

CREATE TABLE HRIS_FILES
  (
    FILE_ID          NUMBER(7,0),
    FILE_NAME        VARCHAR2(255 BYTE),
    FILE_IN_DIR_NAME VARCHAR2(4000 BYTE),
    UPLOADED_DATE    DATE,
    UPLOADED_BY      NUMBER(7,0)
  );
ALTER TABLE HRIS_JOB_HISTORY ADD FILE_ID NUMBER(7,0);

ALTER TABLE HRIS_EMPLOYEE_LEAVE_ASSIGN ADD FISCAL_YEAR_MONTH_NO NUMBER(2,0);

ALTER TABLE HRIS_ATTENDANCE ADD THUMB_ID NUMBER;

CREATE TABLE HRIS_ATTENDANCE_DATA_LOG
  (
    THUMB_ID     NUMBER,
    STATUS       VARCHAR2(255 BYTE),
    CREATED_DATE DATE
  );
  
DROP TABLE HRIS_ATTENDANCE_COPY;
CREATE TABLE HRIS_ATTENDANCE_COPY AS
SELECT * FROM HRIS_ATTENDANCE;
TRUNCATE TABLE HRIS_ATTENDANCE;
ALTER TABLE HRIS_ATTENDANCE MODIFY(EMPLOYEE_ID NULL);
INSERT INTO HRIS_ATTENDANCE
SELECT * FROM HRIS_ATTENDANCE_COPY;
DROP TABLE HRIS_ATTENDANCE_COPY;




DROP TABLE HRIS_EMP_THUMB_COPY;
CREATE TABLE HRIS_EMP_THUMB_COPY AS
SELECT EMPLOYEE_ID, ID_THUMB_ID FROM HRIS_EMPLOYEES;
BEGIN
UPDATE HRIS_EMPLOYEES
SET ID_THUMB_ID =NULL;
ALTER TABLE HRIS_EMPLOYEES MODIFY(ID_THUMB_ID NUMBER);
  FOR employee IN
  (SELECT * FROM HRIS_EMP_THUMB_COPY
  )
  LOOP
    UPDATE HRIS_EMPLOYEES
    SET ID_THUMB_ID  = employee.ID_THUMB_ID
    WHERE EMPLOYEE_ID= employee.EMPLOYEE_ID;
  END LOOP;
END;
/


ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP DROP COLUMN COMPANY_ID ;
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COMPANY_ID VARCHAR2(4000 BYTE);

ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD BRANCH_ID       VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD DEPARTMENT_ID   VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD DESIGNATION_ID  VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD POSITION_ID     VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD SERVICE_TYPE_ID VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD EMPLOYEE_TYPE   VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD GENDER_ID       VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD EMPLOYEE_ID     VARCHAR2(4000 BYTE);


ALTER TABLE HRIS_COMPANY ADD ADVANCE_DR_ACC_CODE            VARCHAR2(255 BYTE);
ALTER TABLE HRIS_COMPANY ADD ADVANCE_CR_ACC_CODE            VARCHAR2(255 BYTE);

ALTER TABLE HRIS_EMPLOYEES
MODIFY ID_DRIVING_LICENCE_TYPE VARCHAR(30);


DROP TABLE HRIS_PENALIZED_MONTHS;
CREATE TABLE "HRIS_PENALIZED_MONTHS"
  (
    "NO_OF_DAYS"           NUMBER NOT NULL,
    "CREATED_DATE"         DATE,
    "CREATED_BY"           NUMBER(7,0),
    "MODIFIED_DATE"        DATE,
    "MODIFIED_BY"          NUMBER(7,0),
    "COMPANY_ID"           NUMBER(7,0),
    "FISCAL_YEAR_ID"       NUMBER(7,0),
    "FISCAL_YEAR_MONTH_NO" NUMBER(2,0)
  );
INSERT INTO HRIS_BLOOD_GROUPS VALUES (9,'N/A',NULL,'E');
ALTER TABLE HRIS_SERVICE_TYPES ADD TYPE VARCHAR2(64 BYTE) DEFAULT 'PERMANENT' NOT NULL CHECK(TYPE IN ('PERMANENT','PROBATION','CONTRACT','TEMPORARY','RESIGNED','RETIRED'));

ALTER TABLE HRIS_OVERTIME ADD WOD_ID NUMBER(7,0);
ALTER TABLE HRIS_OVERTIME ADD WOH_ID NUMBER(7,0);

ALTER TABLE HRIS_OVERTIME_DETAIL ADD WOD_ID NUMBER(7,0);
ALTER TABLE HRIS_OVERTIME_DETAIL ADD WOH_ID NUMBER(7,0);

CREATE TABLE HRIS_EXCEPTIONS(ID NUMBER,EMPLOYEE_ID NUMBER,ERR VARCHAR2(4000 BYTE),CAUSE VARCHAR2(4000 BYTE));

CREATE OR REPLACE PROCEDURE HRIS_RAISE_ERR(P_EMPLOYEE_ID HRIS_EXCEPTIONS.EMPLOYEE_ID%TYPE,P_ERR HRIS_EXCEPTIONS.ERR%TYPE,P_CAUSE HRIS_EXCEPTIONS.CAUSE%TYPE)
AS
    V_ID NUMBER;
BEGIN
    SELECT NVL(MAX(ID),0)+1 INTO V_ID FROM HRIS_EXCEPTIONS;
    INSERT INTO HRIS_EXCEPTIONS VALUES(V_ID,P_EMPLOYEE_ID,P_ERR,P_CAUSE);
END;
/

ALTER TABLE HRIS_ATTD_DEVICE_MASTER ADD PURPOSE VARCHAR2(3 BYTE) DEFAULT 'I/O' NOT NULL CHECK (PURPOSE IN ('IN','OUT','I/O'));
ALTER TABLE HRIS_ATTD_DEVICE_MASTER MODIFY(DEVICE_LOCATION NULL);
ALTER TABLE HRIS_ATTD_DEVICE_MASTER MODIFY(DEVICE_COMPANY NOT NULL);

BEGIN
  FOR ad IN
  (SELECT * FROM HRIS_ATTD_DEVICE_MASTER
  )
  LOOP
    UPDATE HRIS_ATTD_DEVICE_MASTER
    SET DEVICE_ID=
      (SELECT NVL(MAX(DEVICE_ID),0)+1 FROM HRIS_ATTD_DEVICE_MASTER
      )
    WHERE DEVICE_IP =ad.DEVICE_IP;
  END LOOP;
END;
/
-- CONSTRAINT_NAME =SELECT CONSTRAINT_NAME FROM SYS.USER_CONSTRAINTS WHERE OWNER='JGI7475_HM'  AND TABLE_NAME='HRIS_ATTD_DEVICE_MASTER' AND CONSTRAINT_TYPE='P';
ALTER TABLE HRIS_ATTD_DEVICE_MASTER DROP CONSTRAINT [CONSTRAINT_NAME];
ALTER TABLE HRIS_ATTD_DEVICE_MASTER ADD CONSTRAINT PK_ATTD_DEVICE PRIMARY KEY(DEVICE_ID);

ALTER TABLE HRIS_POSITIONS ADD POSITION_CODE VARCHAR2(15 BYTE);

CREATE TABLE "HRIS_ATTENDANCE_PAYROLL"
  (
    "EMPLOYEE_ID"   NUMBER(7,0),
    "ATTENDANCE_DT" DATE,
    "IN_TIME"       TIMESTAMP (6),
    "OUT_TIME"      TIMESTAMP (6),
    "IN_REMARKS"    VARCHAR2(255 BYTE),
    "OUT_REMARKS"   VARCHAR2(255 BYTE),
    "TOTAL_HOUR" FLOAT(126),
    "LEAVE_ID"       NUMBER(7,0),
    "HOLIDAY_ID"     NUMBER(7,0),
    "TRAINING_ID"    NUMBER(7,0),
    "ID"             NUMBER,
    "TRAVEL_ID"      NUMBER(7,0),
    "SHIFT_ID"       NUMBER(7,0),
    "DAYOFF_FLAG"    CHAR(1 BYTE) DEFAULT 'N',
    "OVERALL_STATUS" CHAR(2 BYTE) DEFAULT 'AB',
    "LATE_STATUS"    CHAR(1 BYTE) DEFAULT 'N',
    "HALFDAY_FLAG"   CHAR(1 BYTE) DEFAULT 'N',
    "GRACE_PERIOD"   CHAR(1 BYTE) DEFAULT NULL,
    "HALFDAY_PERIOD" CHAR(1 BYTE) DEFAULT NULL,
    "TWO_DAY_SHIFT"  CHAR(1 BYTE) DEFAULT 'D',
    "TRAINING_TYPE"  CHAR(1 BYTE)
  ) ;

ALTER TABLE HRIS_LEAVE_MASTER_SETUP
ADD DAY_OFF_AS_LEAVE CHAR(1 BYTE) DEFAULT 'Y' NOT NULL CHECK(DAY_OFF_AS_LEAVE IN ('Y','N'));

ALTER TABLE HRIS_LEAVE_MASTER_SETUP
ADD HOLIDAY_AS_LEAVE CHAR(1 BYTE) DEFAULT 'Y' NOT NULL CHECK(HOLIDAY_AS_LEAVE IN ('Y','N'));


ALTER TABLE HRIS_EMPLOYEES MODIFY(SALARY DEFAULT 0 );

UPDATE HRIS_EMPLOYEES SET SALARY=0 WHERE SALARY IS NULL;
ALTER TABLE HRIS_EMPLOYEES MODIFY(SALARY NOT NULL );

SELECT USER_NAME FROM HRIS_USERS GROUP BY USER_NAME HAVING COUNT(*) >1 ;
ALTER TABLE HRIS_USERS ADD CONSTRAINT HRIS_UNQ_USERNAME UNIQUE(USER_NAME);

CREATE TABLE HRIS_ROLE_CONTROL
  (
    ROLE_ID NUMBER(7,0) NOT NULL,
    CONTROL VARCHAR2(2 BYTE) NOT NULL,
    VAL     NUMBER(7,0)
  );

ALTER TABLE HRIS_SHIFTS ADD IGNORE_TIME CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(IGNORE_TIME IN ('Y','N'));

ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD IGNORE_TIME CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(IGNORE_TIME IN ('Y','N'));

Delete  from HRIS_ROLE_PERMISSIONS where status='D';

ALTER TABLE HRIS_EMPLOYEES ADD GROUP_ID NUMBER(7,0);

ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COND_COMPANY_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_COMPANY_ID IN ('AND','OR'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COND_BRANCH_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_BRANCH_ID IN ('AND','OR'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COND_DEPARTMENT_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_DEPARTMENT_ID IN ('AND','OR'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COND_DESIGNATION_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_DESIGNATION_ID IN ('AND','OR'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COND_POSITION_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_POSITION_ID IN ('AND','OR'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COND_SERVICE_TYPE_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_SERVICE_TYPE_ID IN ('AND','OR'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COND_EMPLOYEE_TYPE VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_EMPLOYEE_TYPE IN ('AND','OR'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COND_GENDER_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_GENDER_ID IN ('AND','OR'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD COND_EMPLOYEE_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_EMPLOYEE_ID IN ('AND','OR'));


ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IN_OPER_COMPANY_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_COMPANY_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IN_OPER_BRANCH_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_BRANCH_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IN_OPER_DEPARTMENT_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_DEPARTMENT_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IN_OPER_DESIGNATION_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_DESIGNATION_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IN_OPER_POSITION_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_POSITION_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IN_OPER_SERVICE_TYPE_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_SERVICE_TYPE_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IN_OPER_EMPLOYEE_TYPE VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_EMPLOYEE_TYPE IN ('IN','NOT IN'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IN_OPER_GENDER_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_GENDER_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD IN_OPER_EMPLOYEE_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_EMPLOYEE_ID IN ('IN','NOT IN'));


ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COND_COMPANY_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_COMPANY_ID IN ('AND','OR'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COND_BRANCH_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_BRANCH_ID IN ('AND','OR'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COND_DEPARTMENT_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_DEPARTMENT_ID IN ('AND','OR'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COND_DESIGNATION_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_DESIGNATION_ID IN ('AND','OR'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COND_POSITION_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_POSITION_ID IN ('AND','OR'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COND_SERVICE_TYPE_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_SERVICE_TYPE_ID IN ('AND','OR'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COND_EMPLOYEE_TYPE VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_EMPLOYEE_TYPE IN ('AND','OR'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COND_GENDER_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_GENDER_ID IN ('AND','OR'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD COND_EMPLOYEE_ID VARCHAR2(3 BYTE) DEFAULT 'AND' NOT NULL CHECK(COND_EMPLOYEE_ID IN ('AND','OR'));


ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD IN_OPER_COMPANY_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_COMPANY_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD IN_OPER_BRANCH_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_BRANCH_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD IN_OPER_DEPARTMENT_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_DEPARTMENT_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD IN_OPER_DESIGNATION_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_DESIGNATION_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD IN_OPER_POSITION_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_POSITION_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD IN_OPER_SERVICE_TYPE_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_SERVICE_TYPE_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD IN_OPER_EMPLOYEE_TYPE VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_EMPLOYEE_TYPE IN ('IN','NOT IN'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD IN_OPER_GENDER_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_GENDER_ID IN ('IN','NOT IN'));
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD IN_OPER_EMPLOYEE_ID VARCHAR2(6 BYTE) DEFAULT 'IN' NOT NULL CHECK(IN_OPER_EMPLOYEE_ID IN ('IN','NOT IN'));


ALTER TABLE HRIS_EMPLOYEES ADD DELETED_DATE DATE;
ALTER TABLE HRIS_EMPLOYEES ADD DELETED_BY NUMBER(7,0);

ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD WHERE_CLAUSE   VARCHAR2(4000 BYTE);
ALTER TABLE HRIS_HOLIDAY_MASTER_SETUP ADD WHERE_CLAUSE VARCHAR2(4000 BYTE);

UPDATE HRIS_FISCAL_YEARS
SET FISCAL_YEAR_NAME=REGEXP_SUBSTR(BS_DATE(TRUNC(START_DATE)), '[^-]+', 1, 1)
  ||'/'
  ||REGEXP_SUBSTR(BS_DATE(TRUNC(END_DATE)), '[^-]+', 1, 1);


ALTER TABLE HRIS_EMPLOYEES ADD ID_ACC_CODE VARCHAR2(30);

ALTER TABLE HRIS_EMPLOYEES MODIFY(SALARY DEFAULT 0);
ALTER TABLE HRIS_EMPLOYEES MODIFY (SALARY NULL);


alter table HRIS_APPRAISAL_STAGE 
add instruction VARCHAR2 (4000);

alter table HRIS_APPRAISAL_HEADING 
add instruction VARCHAR2 (4000);

alter table HRIS_APPRAISAL_QUESTION 
add instruction VARCHAR2 (4000);

ALTER TABLE HRIS_APPRAISAL_STATUS
ADD HR_STRENGTH VARCHAR2 (4000);

ALTER TABLE HRIS_APPRAISAL_STATUS
ADD HR_WEAKNESS VARCHAR2 (4000);

ALTER TABLE HRIS_APPRAISAL_STATUS
ADD HR_AREAS_OF_IMPROVEMENT VARCHAR2 (4000);

ALTER TABLE HRIS_APPRAISAL_STATUS
ADD HR_STEPS_FOR_IMPROVEMENT VARCHAR2 (4000);

ALTER TABLE HRIS_EMPLOYEES ADD IS_CEO CHAR (1 BYTE) DEFAULT 'N' CHECK (IS_CEO IN ('Y','N'));
ALTER TABLE HRIS_EMPLOYEES ADD IS_HR  CHAR (1 BYTE) DEFAULT 'N' CHECK (IS_HR  IN ('Y','N'));
ALTER TABLE HRIS_EMPLOYEES ADD IS_DCEO CHAR (1 BYTE) DEFAULT 'N' CHECK (IS_DCEO IN ('Y','N'));


CREATE TABLE HRIS_OVERTIME_MANUAL
  (
    ID              NUMBER(7,0),
    EMPLOYEE_ID     NUMBER(7,0),
    ATTENDANCE_DATE DATE,
    OVERTIME_HOUR   NUMBER(4,2)
  );



ALTER TABLE HRIS_NEWS_FILE
MODIFY NEWS_ID NULL;

ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD OT_MINUTES          NUMBER;
ALTER TABLE HRIS_ATTENDANCE_DETAIL ADD APPROVED_OT_MINUTES NUMBER;


ALTER TABLE HRIS_LEAVE_MASTER_SETUP
MODIFY DEFAULT_DAYS NUMBER(5,2);


ALTER TABLE HRIS_EMPLOYEES
MODIFY BLOOD_GROUP_ID NUMBER(7) NULL;

ALTER TABLE HRIS_EMPLOYEES
MODIFY RELIGION_ID   NUMBER(7) NULL;


CREATE TABLE "HRIS_LEAVE_YEARS" (
  "LEAVE_YEAR_ID"     NUMBER(7,0),
  "START_DATE"         DATE,
  "END_DATE"           DATE,
  "CREATED_DT"         DATE,
  "MODIFIED_DT"        DATE,
  "STATUS"             CHAR(1),
  "REMARKS"            VARCHAR2(255),
  "LEAVE_YEAR_NAME"   VARCHAR2(10)
);

--  Constraints for Table HRIS_FISCAL_YEARS
--------------------------------------------------------

  ALTER TABLE "HRIS_LEAVE_YEARS" ADD CONSTRAINT "LY_ID_PK" PRIMARY KEY ("LEAVE_YEAR_ID") ENABLE;
 
  ALTER TABLE "HRIS_LEAVE_YEARS" MODIFY ("LEAVE_YEAR_ID" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_YEARS" MODIFY ("START_DATE" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_YEARS" MODIFY ("END_DATE" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_YEARS" MODIFY ("CREATED_DT" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_YEARS" MODIFY ("STATUS" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_YEARS" ADD CONSTRAINT "CON_HLYS" CHECK (STATUS IN ('E','D')) ENABLE;
--------------------------------------------------------


CREATE TABLE "HRIS_LEAVE_MONTH_CODE" (
  "LEAVE_YEAR_ID"         NUMBER(7,0),
  "MONTH_ID"               NUMBER(7,0),
  "MONTH_EDESC"            VARCHAR2(100),
  "MONTH_NDESC"            VARCHAR2(100),
  "FROM_DATE"              DATE,
  "TO_DATE"                DATE,
  "REMARKS"                VARCHAR2(255),
  "CREATED_DT"             DATE,
  "MODIFIED_DT"            DATE,
  "STATUS"                 CHAR(1),
  "YEAR"                   NUMBER DEFAULT 2073,
  "MONTH_NO"               NUMBER DEFAULT 1,
  "LEAVE_YEAR_MONTH_NO"   NUMBER(2,0)
);

--  Constraints for Table HRIS_LEAVE_MONTH_CODE

ALTER TABLE "HRIS_LEAVE_MONTH_CODE" ADD CONSTRAINT "HRIS_LEAVE_MONTH_CODE_PK" PRIMARY KEY ("MONTH_ID") ENABLE;
 
  ALTER TABLE "HRIS_LEAVE_MONTH_CODE" MODIFY ("MONTH_ID" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_MONTH_CODE" MODIFY ("FROM_DATE" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_MONTH_CODE" MODIFY ("TO_DATE" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_MONTH_CODE" MODIFY ("CREATED_DT" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_MONTH_CODE" MODIFY ("STATUS" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_MONTH_CODE" MODIFY ("YEAR" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_MONTH_CODE" MODIFY ("MONTH_NO" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_LEAVE_MONTH_CODE" ADD CONSTRAINT "HLMC_S001" CHECK (STATUS IN ('E','D')) ENABLE;


 ALTER TABLE "HRIS_LEAVE_MONTH_CODE" ADD CONSTRAINT "HLMC_FK" FOREIGN KEY ("LEAVE_YEAR_ID") REFERENCES "HRIS_LEAVE_YEARS" ("LEAVE_YEAR_ID") DISABLE;




ALTER TABLE HRIS_EMPLOYEES
ADD EMPOWER_COMPANY_CODE VARCHAR2(20 BYTE);

ALTER TABLE HRIS_EMPLOYEES
ADD EMPOWER_BRANCH_CODE VARCHAR2(20 BYTE);


-- TO INSERT NEW FISACL YEAR AND MONTH CODE


INSERT INTO HRIS_FISCAL_YEARS
VALUES
(4,
TO_DATE('17-JUL-18','DD-MON-RR'),
TO_DATE('16-JUL-19','DD-MON-RR'),
TRUNC(SYSDATE),
NULL,
'E',
NULL,
'2075/2076'
);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,37,'Shrawan','Shrawan',to_date('17-JUL-18','DD-MON-RR'),to_date('16-AUG-18','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2075,4,1);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,38,'Bhadra','Bhadra',to_date('17-AUG-18','DD-MON-RR'),to_date('16-SEP-18','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2075,5,2);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,39,'Ashwin','Ashwin',to_date('17-SEP-18','DD-MON-RR'),to_date('17-OCT-18','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2075,6,3);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,40,'Kartik','Kartik',to_date('18-OCT-18','DD-MON-RR'),to_date('16-NOV-18','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2075,7,4);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,41,'Mangsir','Mangsir',to_date('17-NOV-18','DD-MON-RR'),to_date('15-DEC-18','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2075,8,5);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,42,'Paush','Paush',to_date('16-DEC-18','DD-MON-RR'),to_date('14-JAN-19','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2075,9,6);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,43,'Magh','Magh',to_date('15-JAN-19','DD-MON-RR'),to_date('12-FEB-19','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2075,10,7);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,44,'Falgun','Falgun',to_date('13-FEB-19','DD-MON-RR'),to_date('14-MAR-19','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2075,11,8);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,45,'Chaitra','Chaitra',to_date('15-MAR-19','DD-MON-RR'),to_date('13-APR-19','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2075,12,9);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,46,'Baishakh','Baishakh',to_date('14-APR-19','DD-MON-RR'),to_date('14-MAY-19','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2076,1,10);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,47,'Jestha','Jestha',to_date('15-MAY-19','DD-MON-RR'),to_date('15-JUN-19','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2076,2,11);

Insert into HRIS_MONTH_CODE (FISCAL_YEAR_ID,MONTH_ID,MONTH_EDESC,MONTH_NDESC,FROM_DATE,TO_DATE,REMARKS,CREATED_DT,MODIFIED_DT,STATUS,YEAR,MONTH_NO,FISCAL_YEAR_MONTH_NO) values 
(4,48,'Ashadh','Ashadh',to_date('16-JUN-19','DD-MON-RR'),to_date('16-JUL-19','DD-MON-RR'),null,TRUNC(SYSDATE),null,'E',2076,3,12);


ALTER TABLE HRIS_EMPLOYEES ADD EMPLOYEE_STATUS VARCHAR2(50 BYTE);

ALTER TABLE HRIS_USERS
ADD FIRST_TIME CHAR(1 BYTE) DEFAULT 'Y' NOT NULL;


ALTER TABLE HRIS_DEPARTMENTS
ADD EXCEPTIONAL VARCHAR2(255 BYTE);

ALTER TABLE HRIS_DESIGNATIONS
ADD  ORDER_NO NUMBER(3,0);

ALTER TABLE HRIS_ATTD_DEVICE_MASTER
ADD CONSTRAINT UNQ_DEV_IP UNIQUE (DEVICE_IP);

ALTER TABLE HRIS_EMPLOYEE_SHIFT_ASSIGN
MODIFY ID NUMBER(7) NOT NULL;


CREATE TABLE HRIS_SUB_MAN_BYPASS(
	EMPLOYEE_ID NUMBER(7,0) NOT NULL,
	LEAVE_ID NUMBER (7,0) NOT NULL);



select * from user_constraints where table_name='HRIS_EMPLOYEE_LEAVE_REQUEST'
and constraint_type='C';

/

--alter table HRIS_EMPLOYEE_LEAVE_REQUEST drop constraint CONSTRAINT NAME --;
/

ALTER TABLE HRIS_EMPLOYEE_LEAVE_REQUEST 
ADD CONSTRAINTS LEAVE_REQ_STATUS_CHK CHECK (STATUS IN ('RQ','RC','AP','R','C','CP','CR'));


ALTER TABLE HRIS_EMPLOYEE_LEAVE_REQUEST
ADD CANCEL_REC_BY NUMBER(7,0);

ALTER TABLE HRIS_EMPLOYEE_LEAVE_REQUEST
ADD CANCEL_APP_BY NUMBER(7,0);

ALTER TABLE HRIS_EMPLOYEE_LEAVE_REQUEST
ADD CANCEL_REC_DT DATE;

ALTER TABLE HRIS_EMPLOYEE_LEAVE_REQUEST
ADD CANCEL_APP_DT DATE;


-- disable trigger TRG_SYNC_EMPLOYEE  first if avaiable  important!!

create table HRIS_SALARY_BAKUP
as
select employee_id,salary from hris_employees;

update HRIS_EMPLOYEES set salary=null;

ALTER TABLE HRIS_EMPLOYEES
MODIFY SALARY NUMBER(11,2);

update HRIS_EMPLOYEES m set m.salary=(
select b.salary from HRIS_SALARY_BAKUP b 
where b.employee_id=m.employee_id) ;

-- now  enable trigger TRG_SYNC_EMPLOYEE  if avaiable  important !!


create table hris_roles_bk as select * from hris_roles;

ALTER TABLE HRIS_ROLES 
DROP COLUMN CONTROL;

ALTER TABLE HRIS_ROLES 
ADD CONTROL VARCHAR2(2 BYTE) DEFAULT 'F' NOT NULL ;

update HRIS_ROLES m set m.CONTROL=(
select b.CONTROL from hris_roles_bk b 
where b.ROLE_ID=m.ROLE_ID) ;



CREATE TABLE HRIS_ROLE_CONTROL
  (
    ROLE_ID NUMBER(7,0) NOT NULL,
    CONTROL VARCHAR2(2 BYTE) NOT NULL,
    VAL     NUMBER(7,0)
  );

ALTER TABLE HRIS_EMPLOYEES
ADD SENIORITY_LEVEL NUMBER(7,0);


-- disable trigger TRG_SYNC_EMPLOYEE  first if avaiable  important!!
UPDATE HRIS_EMPLOYEES E  SET E.SENIORITY_LEVEL=(select  OE.SENIORITY_LEVEL from HR_EMPLOYEE_SETUP OE 
WHERE OE.EMPLOYEE_CODE=TO_CHAR(E.EMPLOYEE_ID)
);
-- now  enable trigger TRG_SYNC_EMPLOYEE  if avaiable  important !!


ALTER TABLE HRIS_EMPLOYEES
ADD ABROAD_ADDRESS VARCHAR2(1000 BYTE);



-- note not need if old exe oly for new start
ALTER TABLE HRIS_ATTENDANCE
ADD  CHECKED CHAR(1 BYTE) DEFAULT 'N' NOT NULL;

UPDATE HRIS_ATTENDANCE
SET CHECKED='Y';

-- note not need if old exe oly for new end


ALTER TABLE HRIS_TRAINING_MASTER_SETUP
ADD DAILY_TRAINING_HOUR NUMBER(6,2);

ALTER TABLE HRIS_EMPLOYEE_LEAVE_ADDITION
ADD TRAINING_ID NUMBER(7,0);


ALTER TABLE HRIS_EMPLOYEE_TRAINING_REQUEST
ADD DAILY_TRAINING_HOUR NUMBER(6,2);




ALTER TABLE hris_branches
ADD  ALLOWANCE NUMBER(10,2) DEFAULT 0 NOT NULL;



CREATE TABLE HRIS_ALTERNATE_R_A
(
EMPLOYEE_ID NUMBER(7,0) NOT NULL,
R_A_ID NUMBER(7,0) NOT NULL,
R_A_FLAG CHAR(1 BYTE) NOT NULL CHECK (R_A_FLAG IN('R','A'))
);


ALTER TABLE HRIS_SHIFTS 
ADD NIGHT_SHIFT CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK (NIGHT_SHIFT IN ('Y','N'));

ALTER TABLE HRIS_POSITIONS 
ADD SHIFT_ALLOWANCE CHAR(1 BYTE) DEFAULT 'N' CHECK (SHIFT_ALLOWANCE IN ('Y','N'));

CREATE TABLE HRIS_LEAVE_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), 
"LEAVE_ID" NUMBER(7,0),
CONSTRAINT FK_leaveFileId FOREIGN KEY (LEAVE_ID)
REFERENCES HRIS_EMPLOYEE_LEAVE_REQUEST(ID)
);

INSERT INTO HRIS_LEAVE_FILES(FILE_ID, FILE_NAME, FILE_IN_DIR_NAME, LEAVE_ID) 
VALUES(1,null, null, null);


ALTER TABLE HRIS_EMPLOYEE_LEAVE_ADDITION
ADD TRAVEL_ID NUMBER(7,0);
 
CREATE TABLE HRIS_TRAVEL_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), 
"TRAVEL_ID" NUMBER(7,0),
CONSTRAINT FK_travelFileId FOREIGN KEY (TRAVEL_ID)
REFERENCES HRIS_EMPLOYEE_TRAVEL_REQUEST(TRAVEL_ID)
);

INSERT INTO HRIS_TRAVEL_FILES(FILE_ID, FILE_NAME, FILE_IN_DIR_NAME, TRAVEL_ID) 
VALUES(1,null, null, null);

ALTER TABLE HRIS_EMPLOYEE_TRAVEL_REQUEST
MODIFY TRANSPORT_TYPE varchar2(255);

--START TRANSPORT TYPES---

CREATE TABLE HRIS_TRANSPORT_TYPES (
    Transport_Id NUMBER(7) NOT NULL,
    Transport_Name varchar2(255),
    Transport_Code varchar2(255)
);

Insert into HRIS_TRANSPORT_TYPES (TRANSPORT_ID,TRANSPORT_NAME,TRANSPORT_CODE) values (1,'Flight','AP');
Insert into HRIS_TRANSPORT_TYPES (TRANSPORT_ID,TRANSPORT_NAME,TRANSPORT_CODE) values (2,'Office Veh','OV');
Insert into HRIS_TRANSPORT_TYPES (TRANSPORT_ID,TRANSPORT_NAME,TRANSPORT_CODE) values (3,'Hired Veh','TI');
Insert into HRIS_TRANSPORT_TYPES (TRANSPORT_ID,TRANSPORT_NAME,TRANSPORT_CODE) values (4,'Public Tpt','BS');
Insert into HRIS_TRANSPORT_TYPES (TRANSPORT_ID,TRANSPORT_NAME,TRANSPORT_CODE) values (5,'On Foot','F');
Insert into HRIS_TRANSPORT_TYPES (TRANSPORT_ID,TRANSPORT_NAME,TRANSPORT_CODE) values (6,'Office Bike','OB');
Insert into HRIS_TRANSPORT_TYPES (TRANSPORT_ID,TRANSPORT_NAME,TRANSPORT_CODE) values (7,'Others','O');

-----END TRANSPORT TYPES----------

CREATE TABLE HRIS_RELATIONS(
                               RELATION_ID NUMBER(7,0) PRIMARY KEY,
                               RELATION_NAME VARCHAR2(255 BYTE) NOT NULL,
                               STATUS CHAR(1 BYTE) DEFAULT 'E' CHECK(STATUS IN  ('E','D')) ,
                               CREATED_BY NUMBER(7,0),
                               CREATED_DT DATE DEFAULT TRUNC(SYSDATE),
                               DELETED_BY DATE,
                               DELETED_DT DATE
);

INSERT INTO HRIS_RELATIONS VALUES (1,'SPOUSE','E',NULL,TRUNC(SYSDATE),NULL,NULL);
INSERT INTO HRIS_RELATIONS VALUES (2,'FATHER','E',NULL,TRUNC(SYSDATE),NULL,NULL);
INSERT INTO HRIS_RELATIONS VALUES (3,'MOTHER','E',NULL,TRUNC(SYSDATE),NULL,NULL);
INSERT INTO HRIS_RELATIONS VALUES (4,'DAUGHTER','E',NULL,TRUNC(SYSDATE),NULL,NULL);
INSERT INTO HRIS_RELATIONS VALUES (5,'SON','E',NULL,TRUNC(SYSDATE),NULL,NULL);
INSERT INTO HRIS_RELATIONS VALUES (6,'OTHERS','E',NULL,TRUNC(SYSDATE),NULL,NULL);

CREATE TABLE HRIS_EMPLOYEE_RELATION
(
    E_R_ID NUMBER(7,0) NOT NULL,
    EMPLOYEE_ID NUMBER(7,0) NOT NULL REFERENCES HRIS_EMPLOYEES (EMPLOYEE_ID),
    RELATION_ID NUMBER(7,0) NOT NULL REFERENCES HRIS_RELATIONS (RELATION_ID),
    PERSON_NAME VARCHAR2(255 BYTE) NOT NULL,
    IS_NOMINEE CHAR (1 BYTE) NOT NULL CHECK (IS_NOMINEE IN ('Y','N')),
    IS_DEPENDENT CHAR (1 BYTE) NOT NULL CHECK(IS_DEPENDENT IN ('Y','N')),
    DOB DATE,
    STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS IN ('E','D')),
    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)
);


-- FOR MEDICAL REMBRISUMENT START

CREATE TABLE HRIS_MEDICAL(
MEDICAL_ID NUMBER(7,0) PRIMARY KEY,
EMPLOYEE_ID NOT NULL REFERENCES HRIS_EMPLOYEES (EMPLOYEE_ID),
CLAIM_OF CHAR(1 BYTE) DEFAULT 'S' check(CLAIM_OF IN ('S','D')),
E_R_ID NUMBER(7,0),
AGE NUMBER(7,0),
OPERATION_FLAG CHAR(1 BYTE) NOT NULL check(OPERATION_FLAG IN ('Y','N')),
TRANSACTION_DT DATE  NOT NULL,
REQUESTED_BY NOT NULL REFERENCES HRIS_EMPLOYEES (EMPLOYEE_ID),
REQUESTED_DT DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
REQUESTED_AMT FLOAT(126),
APPROVED_AMT FLOAT(126),
APPROVED_DT DATE,
APPROVED_BY NUMBER(7,0),
BILL_STATUS CHAR(2 BYTE) NOT NULL CHECK(BILL_STATUS IN ('RQ','AP','PD','C')),
BANK_TRANSFER_DT DATE,
TRANSFERRED_BY NUMBER(7,0),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS IN ('E','D')),
REMARKS VARCHAR2(300 BYTE),
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
);


CREATE TABLE HRIS_MEDICAL_BILL
(
MEDICAL_ID NUMBER(7,0) NOT NULL REFERENCES HRIS_MEDICAL (MEDICAL_ID),
SERIAL_NO NUMBER(1,0),
BILL_NO VARCHAR2(10) NOT NULL,
BILL_DATE DATE NOT NULL,
BILL_AMT FLOAT(126) NOT NULL
);

--FOR MEDICAL REMBRUSHMENT END
--!!!! only for soaltee
--  for soaltee where no branch start

select * from User_Constraints where table_name='HRIS_EMPLOYEE_TRAININGS' and Constraint_Type='R'
and Constraint_Name='FK_EMPLOYEE_TRA_BRANCH_BNC_ID';

alter table HRIS_EMPLOYEE_TRAININGS 
drop constraint FK_EMPLOYEE_TRA_BRANCH_BNC_ID;

select * from User_Constraints where
table_name='HRIS_EMPLOYEE_EXPERIENCES' and
Constraint_Type='R'
and Constraint_Name='FK_EMPLOYEE_EXE_BRANCH_BNC_ID';

alter table HRIS_EMPLOYEE_EXPERIENCES 
drop constraint FK_EMPLOYEE_EXE_BRANCH_BNC_ID;

-- for soaltee where no branch stop


INSERT INTO HRIS_PREFERENCES VALUES ('STAFF_MEDICAL','300000');
INSERT INTO HRIS_PREFERENCES VALUES ('STAFF_DEP_MEDICAL','30000');
INSERT INTO HRIS_PREFERENCES VALUES ('STAFF_DEP_OPERATION','30000');

ALTER TABLE hris_leave_master_setup
ADD IS_PROJECT CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK (IS_PROJECT IN  ('Y','N')) ;


CREATE TABLE "HRIS_EMP_SELF_LEAVE_CLOSING" 
   (	"EMPLOYEE_ID" NUMBER(7,0), 
	"LEAVE_ID" NUMBER(7,0), 
	"ENCASH_DAYS" FLOAT(126), 
	"CARRY_FORWARD_DAYS" FLOAT(126), 
	"CREATED_DATE" DATE, 
	"MODIFED_DT" DATE, 
	"STATUS" CHAR(2 BYTE) DEFAULT 'E', 
	"ID" NUMBER(7,0)
   ) ;
--------------------------------------------------------
--  Constraints for Table HRIS_EMP_SELF_LEAVE_CLOSING
--------------------------------------------------------

  ALTER TABLE "HRIS_EMP_SELF_LEAVE_CLOSING" MODIFY ("CREATED_DATE" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_EMP_SELF_LEAVE_CLOSING" ADD CHECK (STATUS IN ('E','D')) ENABLE;

Insert into HRIS_FUNCTIONAL_TYPES (FUNCTIONAL_TYPE_ID,FUNCTIONAL_TYPE_CODE,FUNCTIONAL_TYPE_EDESC,FUNCTIONAL_TYPE_LDESC,CREATED_DT,CREATED_BY,MODIFIED_DT,MODIFIED_BY,DELETED_DT,DELETED_BY,STATUS) values (2,'2','Staff',null,to_date('10-APR-19','DD-MON-RR'),1,null,null,null,null,'E');
Insert into HRIS_FUNCTIONAL_TYPES (FUNCTIONAL_TYPE_ID,FUNCTIONAL_TYPE_CODE,FUNCTIONAL_TYPE_EDESC,FUNCTIONAL_TYPE_LDESC,CREATED_DT,CREATED_BY,MODIFIED_DT,MODIFIED_BY,DELETED_DT,DELETED_BY,STATUS) values (1,'1','Field Staff',null,to_date('10-APR-19','DD-MON-RR'),1,null,null,null,null,'E');


ALTER TABLE HRIS_EMPLOYEE_TRAVEL_REQUEST
ADD DEPARTURE VARCHAR2(255 BYTE);


-- for gws only start
Insert into HRIS_MENUS (MENU_CODE,MENU_ID,MENU_NAME,PARENT_MENU,MENU_DESCRIPTION,ROUTE,STATUS,CREATED_DT,MODIFIED_DT,ICON_CLASS,ACTION,MENU_INDEX,CREATED_BY,MODIFIED_BY,IS_VISIBLE) values ('909',3064,'Leave Self Claim',2,null,'leavecarryforward','E',to_date('18-APR-19','DD-MON-RR'),null,'fa fa-pencil','index',909,1,null,'Y');

Insert into HRIS_LEAVE_MASTER_SETUP (LEAVE_ID,LEAVE_CODE,LEAVE_ENAME,LEAVE_LNAME,ALLOW_HALFDAY,FISCAL_YEAR,CARRY_FORWARD,CASHABLE,CREATED_DT,MODIFIED_DT,STATUS,REMARKS,CREATED_BY,MODIFIED_BY,IS_SUBSTITUTE,PAID,MAX_ACCUMULATE_DAYS,DEFAULT_DAYS,ALLOW_GRACE_LEAVE,IS_MONTHLY,IS_SUBSTITUTE_MANDATORY,ASSIGN_ON_EMPLOYEE_SETUP,IS_PRODATA_BASIS,ENABLE_SUBSTITUTE,DEDUCTION_PRIORITY_NO,COMPANY_ID,BRANCH_ID,DEPARTMENT_ID,DESIGNATION_ID,POSITION_ID,SERVICE_TYPE_ID,EMPLOYEE_TYPE,GENDER_ID,EMPLOYEE_ID,DAY_OFF_AS_LEAVE,HOLIDAY_AS_LEAVE,COND_COMPANY_ID,COND_BRANCH_ID,COND_DEPARTMENT_ID,COND_DESIGNATION_ID,COND_POSITION_ID,COND_SERVICE_TYPE_ID,COND_EMPLOYEE_TYPE,COND_GENDER_ID,COND_EMPLOYEE_ID,IN_OPER_COMPANY_ID,IN_OPER_BRANCH_ID,IN_OPER_DEPARTMENT_ID,IN_OPER_DESIGNATION_ID,IN_OPER_POSITION_ID,IN_OPER_SERVICE_TYPE_ID,IN_OPER_EMPLOYEE_TYPE,IN_OPER_GENDER_ID,IN_OPER_EMPLOYEE_ID,WHERE_CLAUSE,IS_PROJECT) values (8,null,'Project Leave',null,'Y','4','Y','Y',to_date('10-APR-19','DD-MON-RR'),null,'E',null,1,null,'N','Y',5,5,'N','N','N','N','N','N',null,null,null,null,null,null,null,null,null,null,'Y','Y','AND','AND','AND','AND','AND','AND','AND','AND','AND','IN','IN','IN','IN','IN','IN','IN','IN','IN',null,'Y');
-- for gws only end


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)
);


ALTER TABLE HRIS_BRANCHES 
ADD BRANCH_MANAGER_ID NUMBER(7,0);

ALTER TABLE HRIS_ATTD_DEVICE_MASTER 
ADD BRANCH_MANAGER_ID NUMBER(7,0);

-- !!!!  NO NEED TO INSERT START
CREATE TABLE HRIS_WEEKLY_ROASTER
(
EMPLOYEE_ID NUMBER(7,0) REFERENCES HRIS_EMPLOYEES(EMPLOYEE_ID),
SUN NUMBER(7,0),
MON NUMBER(7,0),
TUE NUMBER(7,0),
WED NUMBER(7,0),
THU NUMBER(7,0),
FRI NUMBER(7,0),
SAT NUMBER(7,0),
STATUS CHAR(1 BYTE) DEFAULT 'E' NOT NULL CHECK (STATUS IN ('E','D')),
CREATED_DT DATE DEFAULT TRUNC(SYSDATE) NOT NULL,
CREATED_BY NUMBER(7,0),
MODIFIED_DT DATE,
MODIFIED_BY NUMBER(7,0),
DELETED_DT DATE,
DELETED_BY NUMBER(7,0)
);
-- !!!!  NO NEED TO INSERT END

ALTER TABLE HRIS_EMPLOYEE_LEAVE_REQUEST
ADD SUB_REF_ID NUMBER(7,0);


INSERT INTO  Hris_Preferences VALUES ('SUB_LEAVE_REFERENCE','N'); -- for enableing change N to Y
--INSERT INTO  Hris_Preferences VALUES ('SUB_LEAVE_MAX_DAYS','21');

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,
NEW_LOAN_REQ_ID NUMBER(7,0),
REMARKS VARCHAR2(500)
);

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

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_LEAVE_MASTER_SETUP 
ADD DOCS_COMP_DAYS NUMBER(2,0);

ALTER TABLE HRIS_LEAVE_MASTER_SETUP 
ADD DOCUMENT_REQUIRED VARCHAR2(2);


-- ONLY FOR SOALTEE START
insert into  Hris_Preferences
values ('COMPANY_NAME','SOALTEE CROWN PLAZA');

insert into  Hris_Preferences
values ('COMPANY_ADDRESS','Tachal Kathmandu');

insert into  Hris_Preferences
values ('COMPANY_ACCOUNT_NO','0101011126901');

-- ONLT FOR SOALTEE END




-- leave encash lanet

CREATE TABLE "HRIS_EMP_SELF_LEAVE_CLOSING" 
   (	"EMPLOYEE_ID" NUMBER(7,0), 
	"LEAVE_ID" NUMBER(7,0), 
	"ENCASH_DAYS" Float(126), 
	"CARRY_FORWARD_DAYS" Float(126), 
	"CREATED_DATE" DATE, 
	"MODIFED_DT" DATE, 
	"STATUS" CHAR(2 BYTE) DEFAULT 'E', 
	"ID" NUMBER(7,0)
   );

 ALTER TABLE "HRIS_EMP_SELF_LEAVE_CLOSING" MODIFY ("CREATED_DATE" NOT NULL ENABLE);
 
  ALTER TABLE "HRIS_EMP_SELF_LEAVE_CLOSING" ADD CHECK (STATUS IN ('E','D')) ENABLE;


-- leave encash

alter table HRIS_EMP_TRAVEL_EXPENSE_DTL modify TRANSPORT_TYPE 
char(2) check (TRANSPORT_TYPE IN ('AP','OV','TI','BS','OF'));


update Hris_Preferences set value = 'SOALTEE CROWN PLAZA' where key = 'COMPANY_NAME';
update Hris_Preferences set value = 'Tachal, Kathmandu' where key = 'COMPANY_ADDRESS';


------for Provinces-----------

CREATE TABLE HRIS_PROVINCES
  (
    PROVINCE_ID   NUMBER(7) NOT NULL,
    PROVINCE_NAME VARCHAR2(150),
    STATUS        CHAR(1)
  );
  
Insert into HRIS_PROVINCES (PROVINCE_ID,PROVINCE_NAME,STATUS) values (1,'Province No. 1','E');
Insert into HRIS_PROVINCES (PROVINCE_ID,PROVINCE_NAME,STATUS) values (2,'Province No. 2','E');
Insert into HRIS_PROVINCES (PROVINCE_ID,PROVINCE_NAME,STATUS) values (3,'Province No. 3','E');
Insert into HRIS_PROVINCES (PROVINCE_ID,PROVINCE_NAME,STATUS) values (4,'Gandaki','E');
Insert into HRIS_PROVINCES (PROVINCE_ID,PROVINCE_NAME,STATUS) values (5,'Province No. 5','E');
Insert into HRIS_PROVINCES (PROVINCE_ID,PROVINCE_NAME,STATUS) values (6,'Karnali','E');
Insert into HRIS_PROVINCES (PROVINCE_ID,PROVINCE_NAME,STATUS) values (7,'Sudurpashchim','E');


-- ALTER TABLE HRIS_ZONES 
-- ADD PROVINCE_ID NUMBER(7);

-- ALTER TABLE HRIS_DISTRICTS 
-- ADD PROVINCE_ID NUMBER(7);

ALTER TABLE HRIS_BRANCHES 
ADD PROVINCE_ID NUMBER(7);

ALTER TABLE HRIS_EMPLOYEES 
ADD ADDR_PERM_PROVINCE_ID NUMBER(7);

ALTER TABLE HRIS_EMPLOYEES 
ADD ADDR_TEMP_PROVINCE_ID NUMBER(7);

-------end for Provinces-----------


-- FOR BESTCASE SHIFT SETUP START

CREATE TABLE HRIS_BEST_CASE_SETUP
  (
    CASE_ID            NUMBER(7,0) PRIMARY KEY,
    CASE_NAME          VARCHAR2(100 BYTE),
    START_DATE         DATE,
    END_DATE           DATE,
    STATUS             CHAR(1 BYTE) DEFAULT 'E' NOT NULL CHECK(STATUS IN ('E','D')),
    CREATED_DT         DATE,
    CREATED_BY         NUMBER(7,0),
    MODIFIED_DT        DATE,
    MODIFIED_BY        NUMBER(7,0),
    DELETED_DT         DATE,
    DELETED_BY         NUMBER(7,0)
  );


CREATE TABLE HRIS_BEST_CASE_SHIFT_MAP
(
     CASE_ID            NUMBER(7,0) REFERENCES HRIS_BEST_CASE_SETUP (CASE_ID),
     SHIFT_ID           NUMBER(7,0) REFERENCES HRIS_SHIFTS (SHIFT_ID)
);

CREATE TABLE HRIS_BEST_CASE_EMP_MAP
(
     CASE_ID            NUMBER(7,0) REFERENCES HRIS_BEST_CASE_SETUP (CASE_ID),
     EMPLOYEE_ID        NUMBER(7,0) REFERENCES HRIS_EMPLOYEES (EMPLOYEE_ID)
);
-- FOR BESTCASE SHIFT SETUP END

alter table hris_employee_shifts add case_id number CONSTRAINT fk_case_emp REFERENCES hris_best_case_setup(case_id);


ALTER TABLE HRIS_ROLES
ADD HR_APPROVE CHAR(1 BYTE) DEFAULT 'N';

ALTER TABLE HRIS_ROLES
ADD HR_CANCEL CHAR(1 BYTE) DEFAULT 'N';


------START SOALTEE KIOSK API------
CREATE TABLE HRIS_KIOSK_PRINT_STAT
  (
    PRINT_ID    NUMBER(7) NOT NULL ,
    MONTH_ID    NUMBER(7) NOT NULL ,
    EMPLOYEE_ID NUMBER(7) NOT NULL ,
    PRINT_TYPE  CHAR(2)   NOT NULL ,
    LOAN_ID     NUMBER(7),
    SALARY_TYPE_ID NUMBER(7),
    CONSTRAINT PRINT_PK PRIMARY KEY (PRINT_ID),
    CONSTRAINT CHECK_PRINT_TYPE CHECK (PRINT_TYPE IN ('LV', 'LN' , 'PS'))
  );

  ------END SOALTEE KIOSK API------


ALTER TABLE HRIS_LEAVE_MASTER_SETUP ADD HR_ONLY CHAR(1) DEFAULT 'N' CHECK( HR_ONLY IN ('Y','N'));


--------OVERTIME ADDITION and DEDUCTION TABLE--------
create table HRIS_OVERTIME_A_D(
EMPLOYEE_ID NUMBER(7) NOT NULL,
MONTH_ID NUMBER(7) NOT NULL,
ADDITION NUMBER(4),
DEDUCTION NUMBER(4),
MODIFIED_BY NUMBER(7),
MODIFIED_DT DATE
);
---------------END OVERTIME ADDITION and DEDUCTION TABLE--------


ALTER TABLE HRIS_EMPLOYEES
ADD WOH_FLAG CHAR(1)
ADD CONSTRAINT WOH_FLAG CHECK (WOH_FLAG IN ('L','O'));


ALTER TABLE HRIS_EMPLOYEES 
ADD PAY_EMP_TYPE CHAR (3 BYTE) DEFAULT 'NEW' CHECK(PAY_EMP_TYPE IN ('NEW','OLD'));

ALTER TABLE HRIS_EMPLOYEES
ADD OVERTIME_ELIGIBLE CHAR(1);

ALTER TABLE HRIS_LEAVE_MASTER_SETUP
ADD APPLY_LIMIT NUMBER(2);


select 
leave_id,leave_code,leave_ename
,SUBSTR(leave_ename, 1, 3)
,'update hris_leave_master_setup set leave_code='''||SUBSTR(leave_ename, 1, 3)||''' where leave_id='||leave_id||';'
from 
hris_leave_master_setup where status='E';

insert into HRIS_PREFERENCES values ('PROVINCE_WISE_BRANCH_FILTER','N');

alter table hris_shifts add break_deduct_flag char(1) default 'N'
check (break_deduct_flag in ('Y', 'N'));

alter table hris_ss_pay_value_modified
add salary_type_id number default 1 not null 
CONSTRAINT fk_salary_type REFERENCES hris_salary_type(SALARY_TYPE_ID);

alter table hris_employee_leave_request add HARDCOPY_SIGNED_FLAG
char(1) default 'N'
check (HARDCOPY_SIGNED_FLAG IN ('N', 'Y'));

create table HRIS_EMP_WHEREABOUT_ASN
(
    EMPLOYEE_ID NUMBER(7) not null,
    ORDER_BY    NUMBER(4) not null,
    STATUS      CHAR(1)
);

alter table hris_employee_travel_request add HARDCOPY_SIGNED_FLAG
char(1) default 'N'
check (HARDCOPY_SIGNED_FLAG IN ('N', 'Y'));

alter table HRIS_EMPLOYEES add ALLOWANCE NUMBER(9);

ALTER TABLE HRIS_EMPLOYEE_TRAVEL_REQUEST ADD ITNARY_ID NUMBER(7,0);

CREATE TABLE HRIS_EMPLOYEE_LEAVE_DEDUCTION
(
    ID           NUMBER NOT NULL,
    EMPLOYEE_ID  NUMBER(7) NOT NULL ,
    LEAVE_ID     NUMBER(7) NOT NULL,
    DEDUCTION_DT DATE,
    NO_OF_DAYS   FLOAT(126) NOT NULL,
    STATUS       VARCHAR2(2) NOT NULL,
    REMARKS      VARCHAR2(255),
    CREATED_DT  DATE,
    CREATED_BY  NUMBER(7),
    MODIFIED_DT  DATE,
    MODIFIED_BY  NUMBER(7),
    CONSTRAINT ID_PK PRIMARY KEY (ID),
    CONSTRAINT CHECK_STATUS
    CHECK (STATUS IN ('AP', 'C'))
);

ALTER TABLE HRIS_EMPLOYEE_PENALTY_DAYS
ADD LD_ID NUMBER;

------------ FOR LEAVE DEDUCTION END-----------

alter table hris_employee_loan_request add modified_date date;

--------- HRIS BANKS START ----------
alter table HRIS_EMPLOYEES add bank_id number(7);

CREATE TABLE HRIS_BANKS (
BANK_ID number(7) PRIMARY KEY,
BANK_NAME VARCHAR2(255),
COMPANY_ACC_NO VARCHAR2(25),
BRANCH_NAME VARCHAR2(255),
CREATED_BY NUMBER(7),
CREATED_DT DATE,
STATUS char(1) default 'E'
);

insert into HRIS_BANKS (bank_id, bank_name, status) values ((select nvl(max(bank_id),0)+1 from HRIS_BANKS), 'KUMARI BANK', 'E');

--------- HRIS BANKS END ----------

alter table HRIS_TRAINING_MASTER_SETUP add show_as_training varchar2(2) default 'Y';

ALTER TABLE HRIS_ATTENDANCE_REQUEST 
ADD CREATED_BY NUMBER(7,0);

ALTER TABLE HRIS_ATTENDANCE_REQUEST 
ADD NEXT_DAY_OUT CHAR (1 BYTE) DEFAULT 'N' NOT NULL CHECK (NEXT_DAY_OUT IN ('Y', 'N')) ;


alter table hris_employees
add TAX_BASE CHAR (1 BYTE) CHECK ( TAX_BASE IN ('M','U') );


create table HRIS_REC_APP_OVERRIDE
(
    EMPLOYEE_ID  NUMBER(7) not null
        constraint FK_REC_APP_EMPLOYEE_EMP_ID
            references HRIS_EMPLOYEES,
    RECOMMENDER NUMBER(7)
        constraint FK_REC_APP_EMPLOYEE_EMP_ID2
            references HRIS_EMPLOYEES,
    APPROVER  NUMBER(7)
        constraint FK_REC_APP_EMPLOYEE_EMP_ID3
            references HRIS_EMPLOYEES,
    TYPE VARCHAR2(5) NOT NULL ,
    TYPE_ID NUMBER(7) NOT NULL ,
    STATUS       CHAR(2) NOT NULL,
    CREATED_DT   DATE default SYSDATE,
    MODIFIED_DT  DATE,
    CREATED_BY   NUMBER(7),
    MODIFIED_BY  NUMBER(7)
);

alter table HRIS_NEWS_TYPE add DOWNLOAD_FLAG CHAR(1);


alter table HRIS_LEAVE_MASTER_SETUP add
(leave_year VARCHAR2(2) ,
old_leave char(1) DEFAULT 'N' ,
enable_override char(1) DEFAULT 'N',
show_leave_form char(1) default 'N',
view_order number(2,0),
report_show_default char(1) DEFAULT 'Y'
);

alter table hris_roles modify control varchar2(100 byte);

ALTER TABLE HRIS_SERVICE_TYPES
ADD PF_ELIGIBLE CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK (PF_ELIGIBLE IN ('Y','N'));

ALTER TABLE HRIS_EMPLOYEES
ADD DISABLED_FLAG CHAR(1 BYTE) DEFAULT 'N' CHECK ( DISABLED_FLAG IN ('Y','N') );

ALTER TABLE HRIS_BRANCHES
ADD IS_REMOTE CHAR(1 BYTE) DEFAULT 'N' CHECK ( IS_REMOTE IN ('Y','N') ) ;

ALTER TABLE HRIS_BRANCHES
ADD ALLOWANCE_REBATE NUMBER(10,2) ;

ALTER TABLE HRIS_EMPLOYEES ADD GRATUITY_DATE DATE;

alter table hris_ss_pay_value_modified add remarks varchar2(600);

CREATE TABLE HRIS_ETHNICITIES(
ETHNICITY_ID INT NOT NULL PRIMARY KEY,
ETHNICITY VARCHAR2(100) NOT NULL,
REMARKS VARCHAR2(300),
STATUS CHAR(1) DEFAULT 'E'
);

INSERT INTO HRIS_ETHNICITIES(ETHNICITY_ID, ETHNICITY, REMARKS, STATUS) VALUES(1, 'Brahmin', NULL, 'E');
INSERT INTO HRIS_ETHNICITIES(ETHNICITY_ID, ETHNICITY, REMARKS, STATUS) VALUES(2, 'Chettri', NULL, 'E');
INSERT INTO HRIS_ETHNICITIES(ETHNICITY_ID, ETHNICITY, REMARKS, STATUS) VALUES(3, 'Janajati', NULL, 'E');
INSERT INTO HRIS_ETHNICITIES(ETHNICITY_ID, ETHNICITY, REMARKS, STATUS) VALUES(4, 'Newar', NULL, 'E');
INSERT INTO HRIS_ETHNICITIES(ETHNICITY_ID, ETHNICITY, REMARKS, STATUS) VALUES(5, 'Madhesi', NULL, 'E');
INSERT INTO HRIS_ETHNICITIES(ETHNICITY_ID, ETHNICITY, REMARKS, STATUS) VALUES(6, 'Tharu', NULL, 'E');
INSERT INTO HRIS_ETHNICITIES(ETHNICITY_ID, ETHNICITY, REMARKS, STATUS) VALUES(7, 'Dalit', NULL, 'E');
INSERT INTO HRIS_ETHNICITIES(ETHNICITY_ID, ETHNICITY, REMARKS, STATUS) VALUES(8, 'Thakuri', NULL, 'E');
INSERT INTO HRIS_ETHNICITIES(ETHNICITY_ID, ETHNICITY, REMARKS, STATUS) VALUES(9, 'Others', NULL, 'E');

ALTER TABLE HRIS_EMPLOYEES
ADD ETHNICITY_ID INT CONSTRAINT EMP_ETH_FK REFERENCES HRIS_ETHNICITIES(ETHNICITY_ID);

--- start for GWS only
alter table HRIS_EMPLOYEES add last_regiment_corps varchar2(255) default NULL;
alter table HRIS_EMPLOYEES add Last_army_police_no varchar2(20) default NULL;
alter table HRIS_EMPLOYEES add Last_official_rank varchar2(50) default NULL;
alter table HRIS_EMPLOYEES add Enlistment_Date Date default NULL;
alter table HRIS_EMPLOYEES add Discharge_Date Date default NULL;
alter table HRIS_EMPLOYEES add Discharge_Reason varchar2(255) default NULL;

--- end for GWS

alter table HRIS_EMPLOYEE_RELATION add Contact_no varchar2(20);

--training add fields
alter table hris_employee_trainings disable constraint FK_EMPLOYEE_TRA_BRANCH_BNC_ID;

alter table hris_employee_trainings add total_days number;

update hris_employee_trainings set total_days = (to_date - from_date + 1);

alter table hris_employee_trainings add location varchar2(100);

alter table hris_employee_trainings add delivered_by varchar2(100);

alter table hris_employee_trainings add funding number;

alter table hris_employee_trainings add certification varchar2(200);
--end training add fields

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 varchar2(500),
  created_by number,
  created_date date,
  modified_by number,
  modified_date date,
  FOREIGN KEY (employee_id) REFERENCES hris_employees(employee_id),
  primary key (employee_id, FISCAL_YEAR_ID),
  FOREIGN KEY (FISCAL_YEAR_ID) REFERENCES HRIS_FISCAL_YEARS(FISCAL_YEAR_ID)
);

ALTER TABLE HRIS_EMPLOYEES
  MODIFY SALARY NUMBER(12,2);

ALTER TABLE HRIS_EMPLOYEES
  MODIFY ALLOWANCE NUMBER(12,2);

----------only for GWTN------------
insert INTO HRIS_PREFERENCES values ('CLIENT_NAME','GWTN');
----------only for GWTN------------
