This is just a sample code and needs to be tested and verified before use. There is no warranty of the code.
Code: Select all
CREATE TABLE "SYS"."XX_HOLIDAYS"
( "ID" NUMBER,
"NAME" VARCHAR2(10 BYTE),
"START_DATE" DATE,
"END_DATE" DATE,
"ADJUSTED" VARCHAR2(1 BYTE),
PRIMARY KEY ("ID"))
CREATE TABLE "SYS"."XX_LEAVES"
( "ID" NUMBER,
"NAME" VARCHAR2(10 BYTE),
"START_DATE" DATE,
"END_DATE" DATE,
"ADJUSTED" VARCHAR2(1 BYTE),
"LEAVE_DAYS" NUMBER,
"ADJUSTMENT" NUMBER,
PRIMARY KEY ("ID"))
BEGIN
js_holidays_adjustment(p_holiday_id => 1);
END;
CREATE OR REPLACE PROCEDURE js_holidays_adjustment (
p_holiday_id IN NUMBER
) IS
v_leave_date DATE;
v_days NUMBER := 0;
v_leaves_in_holidays NUMBER := 0;
v_dummy VARCHAR2(1);
BEGIN
FOR i IN (
SELECT
id,
start_date,
end_date
FROM
xx_leaves
WHERE
nvl(adjusted, '0') = '0'
) LOOP
dbms_output.put_line('ID ' || i.id);
-- when start leave date between the holiday dates
v_leaves_in_holidays := 0;
v_days := round(i.end_date - i.start_date + 1, 0);
v_leave_date := i.start_date;
FOR j IN 1..v_days LOOP
BEGIN
SELECT
'X'
INTO v_dummy
FROM
xx_holidays
WHERE
id = p_holiday_id
AND nvl(adjusted, '0') = '0'
AND v_leave_date BETWEEN start_date - 1 AND end_date;
v_leaves_in_holidays := v_leaves_in_holidays + 1;
EXCEPTION
WHEN no_data_found THEN
NULL;
END;
v_leave_date := v_leave_date + 1;
END LOOP;
UPDATE xx_leaves
SET
leave_days = v_days - v_leaves_in_holidays,
adjustment = v_leaves_in_holidays,
adjusted = '1'
WHERE
id = i.id;
END LOOP;
-- So once this holiday adjusted for the past it is flagged so next time it will not be adjusted. As sometimes public holidays are later entered into the system while employees already applied for leaves.
UPDATE xx_holidays
SET
adjusted = '1'
WHERE
id = p_holiday_id;
--COMMIT;
END;