-- Vacancy TABLE
CREATE TABLE HRIS_REC_OPENINGS
(   
    "OPENING_ID" DECIMAL(7,0),
	"OPENING_NO" DECIMAL(7,0),
	"START_DATE" DATE NOT NULL ,
	"END_DATE" DATE  NOT NULL ,
	"EXTENDED_DATE" DATE ,
	"INSTRUCTION_EDESC" VARCHAR(5000),
	"INSTRUCTION_NDESC" VARCHAR(5000),
	"STATUS" CHAR(1) DEFAULT 'E',
	"CREATED_BY" DECIMAL(7,0) ,
	"CREATED_DT" DATE ,
	"MODIFIED_BY" DECIMAL(7,0) DEFAULT NULL,
	"MODIFIED_DT" DATE DEFAULT NULL,
	"CHECKED_BY" DECIMAL(7,0) DEFAULT NULL,
	"CHECKED_DT" DATE DEFAULT NULL,
	"APPROVED_BY" DECIMAL(7,0) DEFAULT NULL,
	"APPROVED_DT" DATE DEFAULT NULL ,
	"DELETED_BY" DECIMAL(7,0) DEFAULT NULL ,
	"DELETED_DT" DATE DEFAULT NULL
);        
CREATE TABLE HRIS_REC_VACANCY
(
    VACANCY_ID NUMBER(7,0)  PRIMARY KEY,
    OPENING_ID NUMBER(7,0) NOT NULL,  --FK
    AD_NO VARCHAR(20),
    LEVEL_ID NUMBER(7,0), --FK
    SERVICE_TYPES_ID NUMBER(7,0),  --FK
    SERVICE_EVENTS_ID NUMBER(7,0),  --FK
    VACANCY_EDESC NVARCHAR(2000) NOT NULL,
    VACANCY_NDESC NVARCHAR(2000),
    PAYMENT_FLAG CHAR(1) DEFAULT 'N',
    ETHNICITY_FLAG CHAR(1) DEFAULT 'N',
    POSITION_ID NUMBER(7,0),  --FK HRIS_DESIGNATIONS (DESIGNATION_ID)
    JOB_SPECIFICATION VARCHAR(2000),
    QUALIFICATION_ID NUMBER(7,0),
    ROLES VARCHAR(250),
    RESPONSIBILITY VARCHAR(2000),
    EXPERIENCE VARCHAR(255),
    QUOTA_OPEN NUMBER(2,0) NOT NULL,
    QUOTA_INTERNAL NUMBER(2,0) NOT NULL,
    DEPARTMENT_ID NUMBER(7,0) NOT NULL,  --FK
    REMARKS NVARCHAR(255),
    STATUS CHAR(1) DEFAULT 'E',
    CREATED_BY  NUMBER(7,0),
    CREATED_DT DATE,
    MODIFIED_BY NUMBER(7,0) DEFAULT NULL,
    MODIFIED_DT DATE DEFAULT NULL,
    CHECKED_BY NUMBER (7,0) DEFAULT NULL,
    CHECKED_DT DATE DEFAULT NULL,
    APPROVED_BY NUMBER (7,0) DEFAULT NULL,
    APPROVED_DT DATE DEFAULT NULL,
    DELETED_BY NUMBER(7,0) DEFAULT NULL,
    DELETED_DT DATE DEFAULT NULL
);
CREATE TABLE HRIS_REC_VACANCY_OPTIONS
(
    VACANCY_OPTION_ID NUMBER(7,0) PRIMARY KEY,
    VACANCY_ID NUMBER(7,0) NOT NULL,  --FK
    OPTION_ID NUMBER(7,0) NOT NULL, --FK
    QUOTA NUMBER(2,0) NOT NULL,
    OPEN_INTERNAL CHAR(10),
    NORMAL_AMT NUMBER(7,2), --PRICE
    LATE_AMT NUMBER(7,2), --PRICE
    REMARKS NVARCHAR(255),
    STATUS CHAR(1) NOT NULL,
    CREATED_BY  NUMBER(7,0),
    CREATED_DT DATE,
    MODIFIED_BY NUMBER(7,0) DEFAULT NULL,
    MODIFIED_DT DATE DEFAULT NULL,
    CHECKED_BY NUMBER (7,0) DEFAULT NULL,
    CHECKED_DT DATE DEFAULT NULL,
    APPROVED_BY NUMBER (7,0) DEFAULT NULL,
    APPROVED_DT DATE DEFAULT NULL,
    DELETED_BY NUMBER(7,0) DEFAULT NULL,
    DELETED_DT DATE DEFAULT NULL
);
--  Inclusion
CREATE TABLE HRIS_REC_OPTIONS
(
    OPTION_ID NUMBER(7,0) PRIMARY KEY,
    OPTION_EDESC VARCHAR(255) NOT NULL,
    OPTION_NDESC VARCHAR(255),
    REMARKS VARCHAR(255),
    STATUS CHAR(1) NOT NULL,
    CREATED_BY  NUMBER(7,0),
    CREATED_DT DATE,
    MODIFIED_BY NUMBER(7,0) DEFAULT NULL,
    MODIFIED_DT DATE DEFAULT NULL,
    DELETED_BY NUMBER(7,0) DEFAULT NULL,
    DELETED_DT DATE DEFAULT NULL
);
CREATE TABLE HRIS_REC_OPENINGS_DOCUMENTS
(  
    FILE_ID NUMBER(7,0) PRIMARY KEY, 
    FILE_NAME VARCHAR(255), 
    FILE_IN_DIR_NAME VARCHAR(2000), 
    UPLOADED_DATE DATE DEFAULT CURRENT_DATE, 
    OPENING_ID NUMBER(7,0), --FK
    STATUS CHAR(1) DEFAULT 'E'
);
CREATE TABLE HRIS_REC_STAGES
(
    REC_STAGE_ID NUMBER(2,0) PRIMARY KEY,
    STAGE_EDESC NVARCHAR(255),
    STAGE_NDESC NVARCHAR(255),
    ORDER_NO NUMBER(3,1),
    IS_FINAL CHAR(1) DEFAULT 'N',
    STATUS CHAR(1) DEFAULT 'E',
    CREATED_BY  NUMBER(7,0),
    CREATED_DT DATE,
    MODIFIED_BY NUMBER(7,0) DEFAULT NULL,
    MODIFIED_DT DATE DEFAULT NULL,
    CHECKED_BY NUMBER (7,0) DEFAULT NULL,
    CHECKED_DT DATE DEFAULT NULL,
    APPROVED_BY NUMBER (7,0) DEFAULT NULL,
    APPROVED_DT DATE DEFAULT NULL,
    DELETED_BY NUMBER(7,0) DEFAULT NULL,
    DELETED_DT DATE DEFAULT NULL
);
CREATE TABLE HRIS_REC_VACANCY_STAGES
(
    REC_VACANCY_STAGE_ID NUMBER(7,0) PRIMARY KEY,
    REC_STAGE_ID NUMBER(2,0), --FK
    VACANCY_ID NUMBER(7,0) NOT NULL,  --FK
    REMARKS VARCHAR(255),
    STATUS CHAR(1) NOT NULL,
    CREATED_BY  NUMBER(7,0),
    CREATED_DT DATE,
    MODIFIED_BY NUMBER(7,0) DEFAULT NULL,
    MODIFIED_DT DATE DEFAULT NULL,
    CHECKED_BY NUMBER (7,0) DEFAULT NULL,
    CHECKED_DT DATE DEFAULT NULL,
    APPROVED_BY NUMBER (7,0) DEFAULT NULL,
    APPROVED_DT DATE DEFAULT NULL,
    DELETED_BY NUMBER(7,0) DEFAULT NULL,
    DELETED_DT DATE DEFAULT NULL
);
-- Level
CREATE TABLE HRIS_REC_VACANCY_LEVELS
(
    VACANCY_LEVEL_ID NUMBER (7,0) PRIMARY KEY,
    FUNCTIONAL_LEVEL_ID NUMBER(7,0) NOT NULL, --FK
    POSITION_ID NUMBER(7,0), -- FK
    OPENING_ID NUMBER(7,0), --FK
    EFFECTIVE_DATE DATE DEFAULT CURRENT_DATE,
    NORMAL_AMOUNT NUMBER(7,0) NOT NULL,
    LATE_AMOUNT NUMBER(7,0),
    INCLUSION_AMOUNT NUMBER(7,0),
    MIN_AGE NUMBER(2,0),
    MAX_AGE NUMBER(2,0),
    STATUS CHAR(1) DEFAULT 'E',
    CREATED_BY NUMBER(7,0) NOT NULL,
    CREATED_DATE DATE DEFAULT CURRENT_DATE,
    MODIFIED_BY NUMBER(7,0) NULL,
    MODIFIED_DATE DATE DEFAULT NULL,
    DELETED_BY NUMBER(7,0) NULL,
    DELETED_DATE DATE DEFAULT NULL
);
CREATE TABLE HRIS_REC_VACANCY_INCLUSION
(
    VACANCY_INCLUSION_ID NUMBER (7,0) PRIMARY KEY,
    INCLUSION_ID VARCHAR(20), --FK
    VACANCY_ID NUMBER (7,0),
    STATUS CHAR(1) DEFAULT 'E',
    CREATED_BY NUMBER(7,0) NOT NULL,
    CREATED_DATE DATE DEFAULT CURRENT_DATE,
    MODIFIED_BY NUMBER(7,0) NULL,
    MODIFIED_DATE DATE DEFAULT NULL,
    DELETED_BY NUMBER(7,0) NULL,
    DELETED_DATE DATE DEFAULT NULL
);








-- NOT REQUIRED

CREATE TABLE HRIS_REC_EXAM_CENTERS
(
    EXAM_CENTER_ID NUMBER(7,0) PRIMARY KEY,
    EXAM_CENTER_CODE VARCHAR(10),
    EXAM_CENTER_NAME VARCHAR(100) NOT NULL,
    STATUS CHAR(1) DEFAULT 'E',
    CREATED_DATE DATE DEFAULT CURRENT_DATE
);
CREATE TABLE HRIS_REC_EXAM_TYPES
(
    EXAM_TYPE_ID NUMBER(7,0) PRIMARY KEY,
    EXAM_TYPE_CODE VARCHAR(10),
    EXAM_TYPE_NAME VARCHAR(100) NOT NULL,
    STATUS CHAR(1) DEFAULT 'E',
    CREATED_DATE DATE DEFAULT CURRENT_DATE 
);





-- Add FOREIGN KEY
ALTER TABLE HRIS_REC_VACANCY ADD CONSTRAINT FK_DEPARTMENT_ID FOREIGN KEY (DEPARTMENT_ID) REFERENCES HRIS_DEPARTMENTS (DEPARTMENT_ID);
ALTER TABLE HRIS_REC_VACANCY_OPTIONS ADD CONSTRAINT FK_OPTION_ID FOREIGN KEY (OPTION_ID) REFERENCES HRIS_REC_OPTIONS (OPTION_ID);

ALTER TABLE "HRISVISMA2"."HRIS_REC_VACANCY" ADD CONSTRAINT FK_LEVEL_ID FOREIGN KEY (LEVEL_ID) REFERENCES HRIS_FUNCTIONAL_LEVELS (FUNCTIONAL_LEVEL_ID);

ALTER TABLE "HRISVISMA2"."HRIS_REC_VACANCY" ADD CONSTRAINT FK_SERVICE_TYPES_ID 
FOREIGN KEY (SERVICE_TYPES_ID) REFERENCES HRIS_SERVICE_TYPES (SERVICE_TYPE_ID);

ALTER TABLE "HRISVISMA2"."HRIS_REC_VACANCY" ADD CONSTRAINT FK_INCLUSION_ID 
FOREIGN KEY (INCLUSION_ID) REFERENCES HRIS_REC_OPTIONS (OPTION_ID);


-- Add primary KEY
ALTER TABLE HRIS_DESIGNATIONS  ADD primary key ("DESIGNATION_ID");

ALTER TABLE "HRISVISMA2"."HRIS_SERVICE_TYPES" ADD primary key ("SERVICE_TYPE_ID");

ALTER TABLE "HRISVISMA2"."HRIS_REC_SERVICE_EVENTS_TYPES" ADD primary key ("SERVICE_EVENT_ID");


-- Add Table
ALTER TABLE <tablename> ADD (<columnname> <datatype>);
ALTER TABLE "HRISVISMA2"."HRIS_REC_VACANCY" ADD (SERVICE_TYPES_ID NUMBER(7,0));
ALTER TABLE "HRISVISMA2"."HRIS_REC_VACANCY" ADD (SERVICE_EVENTS_ID NUMBER(7,0));

ALTER TABLE "HRISVISMA2"."HRIS_REC_VACANCY_USERS" ADD (MODIFIED_DT DATE DEFAULT NULL);

MODIFIED_DT DATE DEFAULT NULL

