Public Holidays adjustment during Leaves

Oracle Application Express is a rapid development tool for Web applications on the Oracle database.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Public Holidays adjustment during Leaves

Post by admin »

This procedure is to count public holidays or any type of holidays that you don't want to count in the range of employee leaves and wants to adjust those number of days. For example, you have public holidays from the 4th to the 6th of a month means 3 days are public holidays. If an employee had applied or applied from the 1st to 10th calendar days then 7 leaves will be calculated where the policy is calendar days so weekends are counted.

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;
Malik Sikandar Hayat
admin@erpstuff.com
Post Reply

Who is online

Users browsing this forum: No registered users and 1 guest