CREATE TABLE HRISVISMA2.HRIS_OT(
OT_ID INTEGER NOT NULL PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
YEAR DECIMAL(34),
MONTH DECIMAL(34),
STATUS VARCHAR(2),
CREATED_DT DATE DEFAULT CURRENT_DATE,
MODIFIED_DT DATE DEFAULT CURRENT_DATE,
APPROVED_BY DECIMAL(7)
);


CREATE TABLE HRISVISMA2.HRIS_OT_DETAIL(
OT_DETAIL_ID INTEGER NOT NULL UNIQUE GENERATED BY DEFAULT AS IDENTITY,
OT_ID INTEGER NOT NULL,
EMPLOYEE_ID DECIMAL(7),
APPROVED_REMARKS VARCHAR(255),
DESIGNATION_ID DECIMAL(7),
OTDATE DATE,
IN_TIME TIME,
OUT_TIME TIME,
RAW_OT TIME,
CALC DECIMAL(7),
SATTA_BIDA DECIMAL(7),
SATTA_BIDA_AMOUNT DECIMAL(7),
KHAJA_KARCHA DECIMAL(7),
KHAJA_KHARCHA_10PM DECIMAL(7),
RATRI_VATTA DECIMAL(7),
CONSTRAINT FK_OT_ID FOREIGN KEY (OT_ID) REFERENCES HRISVISMA2.HRIS_OT(OT_ID) ON DELETE RESTRICT
);

ALTER TABLE HRISVISMA2.HRIS_OT ADD (OVERTIME_ID DECIMAL(7) NOT NULL);
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ADD (EXTRA_KHAJA_KARCHA DECIMAL(7));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ALTER(RAW_OT VARCHAR(50));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ALTER(CALC DECIMAL(7,2));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ALTER(SATTA_BIDA DECIMAL(7,2));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ALTER(SATTA_BIDA_AMOUNT DECIMAL(7,2));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ALTER(KHAJA_KARCHA DECIMAL(7,2));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ALTER(KHAJA_KHARCHA_10PM DECIMAL(7,2));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ALTER(RATRI_VATTA DECIMAL(7,2));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ALTER(EXTRA_KHAJA_KARCHA DECIMAL(7,2));

RENAME COLUMN HRISVISMA2.HRIS_OT.YEAR TO FISCAL_YEAR_ID;
RENAME COLUMN HRISVISMA2.HRIS_OT.MONTH TO MONTH_NO;

ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ADD (APPROVED_BY DECIMAL(7));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ADD (STATUS VARCHAR(2));
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ADD (OVERTIME_ID DECIMAL(7) NOT NULL);
ALTER TABLE HRISVISMA2.HRIS_OT DROP (STATUS);
ALTER TABLE HRISVISMA2.HRIS_OT DROP (APPROVED_BY);
ALTER TABLE HRISVISMA2.HRIS_OT DROP (OVERTIME_ID);
ALTER TABLE HRISVISMA2.HRIS_OT ADD (IS_CALCULATED CHAR(1) NOT NULL DEFAULT 'N');
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL DROP (EXTRA_KHAJA_KARCHA);

ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL DROP (SATTA_BIDA);
ALTER TABLE HRISVISMA2.HRIS_OT_DETAIL ADD(SATTA_BIDA DECIMAL(2,1));
