CREATE TABLE HRIS_PAYROLL_JV(
    DEPARTMENT_ID NUMBER,
    FOREIGN KEY (DEPARTMENT_ID) REFERENCES HRIS_DEPARTMENTS(DEPARTMENT_ID),
    PAY_ID NUMBER,
    FOREIGN KEY (PAY_ID) REFERENCES HRIS_PAY_SETUP(PAY_ID),
    JV_NAME VARCHAR(200),
    STATUS CHAR(1) NOT NULL CHECK (STATUS IN('D','E')),
    FLAG CHAR(1) NOT NULL CHECK (FLAG IN('Y','N')),
    PAY_TYPE_FLAG CHAR(1) NOT NULL CHECK (PAY_TYPE_FLAG IN('D','C')),
    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),
    PRIMARY KEY (DEPARTMENT_ID, PAY_ID)
);


CREATE TABLE HRIS_PAY_SETUP_SPECIAL(
    PAY_ID INT,
    FOREIGN KEY (PAY_ID) REFERENCES HRIS_PAY_SETUP(PAY_ID),
    SALARY_TYPE_ID INT,
    FOREIGN KEY (SALARY_TYPE_ID) REFERENCES HRIS_SALARY_TYPE(SALARY_TYPE_ID),
    FORMULA LONG,
    STATUS CHAR(1) NOT NULL CHECK (STATUS IN('D','E')),
    FLAG CHAR(1) CHECK (FLAG IN ('Y', 'N')),
    CREATED_DT DATE DEFAULT TRUNC(SYSDATE),
    CREATED_BY NUMBER(7,0),
    MODIFIED_BY NUMBER(7,0),
    MODIFIED_DT DATE,
    PRIMARY KEY (PAY_ID, SALARY_TYPE_ID)
);
