CREATE TABLE HRIS_WORK_FORCE
(
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),
FUNCTIONAL_TYPE_ID NUMBER(7,0),
GENDER_ID NUMBER(7,0),
ETHNICITY_ID NUMBER(7,0),
NO_OF_QUOTA NUMBER(7,0),
STATUS CHAR(1 BYTE) CHECK(STATUS  IN ('E','D')),
CREATED_BY NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;

CREATE TABLE HRIS_REC_USERS
(
APPLICANT_ID NUMBER(7,0),
FULL_NAME VARCHAR2(100 BYTE) NOT NULL,
EMAIL VARCHAR2(255 BYTE) NOT NULL,
MOBILE_NO VARCHAR2(255) NOT NULL,
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
LOGIN_ID VARCHAR2(255 BYTE) NOT NULL,
LOGIN_PASSWORD  VARCHAR2(100 BYTE) NULL,
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;




CREATE TABLE HRIS_REC_STAGES
(
REC_STAGE_ID NUMBER(2,0),
STAGE_EDESC VARCHAR2(255 BYTE),
ORDER_NO NUMBER(3,1),
IS_FINAL CHAR(1 BYTE) DEFAULT 'N' NOT NULL CHECK(STATUS  IN ('Y','N')),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;

CREATE TABLE HRIS_REC_OPENINGS
(
OPENING_ID NUMBER(7,0),
AD_NO VARCHAR2(100 BYTE),
START_DATE DATE NOT NULL,
END_DATE DATE NOT NULL,
EXTENDED_DATE DATE,
INSTRUNCTION VARCHAR(255 BYTE)
)


CREATE TABLE HRIS_REC_VACCANCY
(
VACCANCY_ID NUMBER(7,0) NOT NULL,
OPENING_ID NUMBER(7,0) NOT NULL,
VACCANCY_EDESC VARCHAR2(255 BYTE) NOT NULL,
VACCANCY_NDESC VARCHAR2(255 BYTE),
PAYMENT_FLAG CHAR(1 BYTE) CHECK(PAYMENT_FLAG IN ('Y','N') ) NOT NULL,
ETHNICITY_FLAG CHAR(1 BYTE) CHECK(ETHNICITY_FLAG IN ('Y','N') ) NOT NULL,
JOB_SPERCIFICATION VARCHAR(255 BYTE),
QUALIFICATION VARCHAR(255 BYTE),
ROLES VARCHAR(255 BYTE),
RESPONSIBILITY VARCHAR(255 BYTE),
EXPERIENCE VARCHAR(255 BYTE),
AGE VARCHAR(255 BYTE),
GENDER NUMBER(1,0),
NO_OF_QUOTA NUMBER(2,0) NOT NULL,
REMARKS VARCHAR2(255 BYTE),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;


CREATE TABLE HRIS_REC_VACCANCY_ETHNICITY
(
ID NUMBER(7,0)NOT NULL,
VACCANCY_ID NUMBER(7,0) NOT NULL,
ETHNICITY_ID NUMBER(7,0) NOT NULL,
NO_OF_QUOTA NUMBER(2,0) NOT NULL,
REMARKS VARCHAR2(255 BYTE),
NORMAL AMT NUMBER(7,2), --PRICE
LATE AMT NUMBER(7,2), --PRICE
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;

CREATE TABLE HRIS_VACCANCY_STAGES
(
REC_STAGE_ID NUMBER(2,0) NOT NULL,
VACCANCY_ID NUMBER(7,0) NOT NULL,
REMARKS VARCHAR2(255 BYTE),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;

CREATE TABLE HRIS_REC_PAY_TYPE
(
PAY_TYPE_ID NUMBER(2,0) NOT NULL,
PAY_TYPE_NAME VARCHAR2(255 BYTE),
REMARKS VARCHAR2(255 BYTE),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;


CREATE TABLE HRIS_REC_VACCANCY_DTL
(
APPLICATION_ID NUMBER(7,0) NOT NULL,
VACCANCY_ID NUMBER(7,0) NOT NULL,
APPLICANT_ID NUMBER(7,0) NOT NULL,
APPLICATION_NO VARCHAR2(255 BYTE) NOT NULL,
EXAM_ROLL_NO VARCHAR2(255 BYTE) NOT NULL,
PAYMENT_VERIFICATION_FLAG CHAR(1 BYTE) DEFAULT  'N' NOT NULL CHECK(PAYMENT_VERIFICATION_FLAG  IN ('Y','N')),
FORM_VERIFICATION_FLAG CHAR(1 BYTE) DEFAULT  'N' NOT NULL CHECK(FORM_VERIFICATION_FLAG  IN ('Y','N')),
REMARKS VARCHAR2(255 BYTE),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;

CREATE TABLE HRIS_REC_FILES
(
REC_FILE_ID NUMBER(7,0) NOT NULL,
FILE_PATH VARCHAR2(255) NOT NULL,
FILE_NAME VARCHAR2(255) NOT NULL,
UPLOAD_TYPE CHAR(4 BYTE) CHECK (UPLOAD_TYPE IN ('VACO','VACM','VACP','VACS')),
UPLOAD_ID NUMBER(7,0),
ORDER_NO NUMBER(2,0),
REMARKS VARCHAR2(255 BYTE),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;


CREATE TABLE HRIS_REC_VACCANCY_PAYMENT_DTL
(
APPLICATION_ID NUMBER(7,0) NOT NULL,
PAY_TYPE_ID NUMBER(2,0) NOT NULL,
ETHNICITY_ID NUMBER(7,0) NOT NULL,
REC_FILE_ID NUMBER(7,0) NOT NULL,
PAID_AMT NUMBER(10,2),
PAYMENT_VERIFICATION_FLAG CHAR(1 BYTE) DEFAULT  'N' NOT NULL CHECK(PAYMENT_VERIFICATION_FLAG  IN ('Y','N')),
REMARKS VARCHAR2(255 BYTE),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
VERIFIED_BY NUMBER(7,0),
VERIFIED_DT DATE,
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;


CREATE TABLE HRIS_REC_STAGE_DTL
(
STAGE_DTL_ID NUMBER(7,0) NOT NULL,
APPLICATION_ID NUMBER(7,0) NOT NULL,
VACCANCY_ID NUMBER(7,0) NOT NULL,
REC_STAGE_ID NUMBER(2,0) NOT NULL,
ORDER_NO NUMBER(5,0),
SCORE VARCHAR2(100 BYTE) NOT NULL,
PASSED_FLAG CHAR(1 BYTE) DEFAULT  'N' NOT NULL CHECK(PASSED_FLAG  IN ('Y','N')),
ALTERNATE_FLAG CHAR(1 BYTE) DEFAULT  'N' NOT NULL CHECK(ALTERNATE_FLAG  IN ('Y','N')),
REMARKS VARCHAR2(255 BYTE),
STATUS CHAR(1 BYTE) NOT NULL CHECK(STATUS  IN ('E','D')),
VERIFIED_BY NUMBER(7,0),
VERIFIED_DT DATE,
CREATED_BY  NUMBER(7,0),
CREATED_DT DATE,
MODIFIED_BY NUMBER(7,0),
MODIFIED_DT DATE,
DELETED_BY NUMBER(7,0),
DELETED_DT DATE
) ;












