Function to calculate number of absence days in the year per month and calculation (Maternity Leave)

 number of days of given month and calc for maternity leave. 

for 45 + 45 days of maternity leave , employee is eligible for 52.5 days full Paid and then no paid. 

The leave periods are as follows:

First period: 19th Aug 2024 to 2nd Oct 2024 (45 days)

Second period: 3rd Oct 2024 to 16th Nov 2024 (45 days)

August month 13 days full paid., 0 days unpaid

September month 30 days full paid , 0 days unpaid

October month 2 days full paid, 15 days half paid. i.e., total fullPaid 9.5 days ( 2 + 15/2), unpaid 21.5 days

November month 0 days paid. i.e., 30 days unpaid

FUNCTION GET_MATERNITY_UNPAID_DAYS_F (p_assignment_id   NUMBER,
                                      P_PROC_START      DATE)
    RETURN NUMBER
AS
    l_days_taken    NUMBER := 0;
    l_days_unpaid   NUMBER := 0;
    p_month         NUMBER := TO_NUMBER (TO_CHAR (P_PROC_START, 'mm'));
    p_year          NUMBER := TO_NUMBER (TO_CHAR (P_PROC_START, 'yyyy'));
BEGIN
   <<outer_loop>>
    FOR rec
        IN (  SELECT date_start, date_end
                FROM per_absence_attendances
               WHERE     person_id = (SELECT person_id
                                        FROM per_assignments_x
                                       WHERE assignment_id = p_assignment_id)
                     AND ABSENCE_ATTENDANCE_TYPE_ID = 68
                     AND date_start >=
                         TO_DATE ('01-01-' || TO_CHAR (p_year), 'dd-mm-yyyy')
                     AND date_start <
                         TO_DATE ('01-01-' || TO_CHAR (p_year + 1),
                                  'dd-mm-yyyy')
                     AND date_start <=
                         ADD_MONTHS (
                             TO_DATE (
                                    '01-'
                                 || TO_CHAR (p_month)
                                 || '-'
                                 || TO_CHAR (p_year),
                                 'dd-mm-yyyy'),
                             1) --ignore anything that starts after month in question
            ORDER BY date_start)
    LOOP
        FOR rec2 IN (    SELECT rec.date_start + ROWNUM - 1     AS calc_date
                           FROM DUAL
                     CONNECT BY LEVEL <= rec.date_end - rec.date_start + 1)
        LOOP
            l_days_taken := l_days_taken + 1;

            IF TRUNC (rec2.calc_date, 'MM') =
               ADD_MONTHS (
                   TO_DATE ('01-' || p_month || '-' || p_year, 'dd-mm-yyyy'),
                   1)
            THEN
                EXIT outer_loop;          --gone past month in question, stop.
            ELSIF TRUNC (rec2.calc_date, 'MM') =
                  TO_DATE ('01-' || p_month || '-' || p_year, 'dd-mm-yyyy')
            THEN
                IF l_days_taken = 53
                THEN
                    --hit the end of the allocation here, so half day
                    l_days_unpaid := l_days_unpaid + 0.5;
                ELSIF l_days_taken >= 54
                THEN
                    --past the last half day, take a full day unpaid
                    l_days_unpaid := l_days_unpaid + 1;
                END IF;
            END IF;
        END LOOP;
    END LOOP;

    RETURN l_days_unpaid;
END;