Second Last Workday Query in Oracle Fusion

SELECT CASE 
            WHEN (
                    SELECT 1
                    FROM per_resource_exceptions pre
                    WHERE trunc(pre.start_date_time) = last_working_day
                    ) = 1
                THEN last_working_day - 2   --If last working day is exceptional holiday
            ELSE 
            decode(
                to_char(last_working_day, 'D'), 
                    '2', last_working_day - 3, --If LWD is Monday 
                    '3', last_working_day - 1, --If LWD is Tuesday
                    '4', last_working_day - 1, --If LWD is Wednesday
                    '5', last_working_day - 1, --If LWD is Thursday
                    '6', last_working_day - 1, --If LWD is Friday
                last_working_day)
            END second_last_working_day
        ,to_char(last_working_day, 'YYYY-MM-DD') last_working_day
    FROM (
            SELECT CASE 
                -- IF last day is public holiday
                WHEN (
                        SELECT 1
                        FROM per_calendar_events_vl pce
                        WHERE pce.category = 'PH'
                            --AND pce.coverage_type = 'O'
                            AND trunc(pce.start_date_time) = trunc(
                                    decode(
                                        to_char(last_day(to_char(trunc(sysdate))), 'D'), --get the last day of the month and then get the day of the week, i.e. 1 to 7
                                        '1', last_day(to_char(trunc(sysdate))) - 2, --If day of week is 1, i.e. Sunday then last day would be obtained by substracting 2
                                        '7', last_day(to_char(trunc(sysdate))) - 1, --If day of week is 7, i.e. Saturday then last day would be obtained by substracting 1
                                        last_day(to_char(trunc(sysdate)))
                                    )
                                )
                    ) = 1
                    THEN CASE
                            --Check if previous working day is sunday 
                            WHEN to_char(
                                            (
                                                SELECT 
                                                trunc(
                                                    decode( to_char(last_day(sysdate), 'D'), 
                                                            '1', last_day(to_char(trunc(sysdate))) - 2, 
                                                            '7', last_day(to_char(trunc(sysdate))) - 1, 
                                                            last_day(to_char(trunc(sysdate))))
                                                ) - 1   --substract -1 to get previous working day
                                                FROM fnd_cal_day
                                                WHERE trunc(report_date) = trunc(sysdate)
                                            ), 'D'
                                        ) = 1
                                THEN (
                                        SELECT (
                                            trunc(
                                                    decode( to_char(last_day(sysdate), 'D'), 
                                                            '1', last_day(to_char(trunc(sysdate))) - 2, 
                                                            '7', last_day(to_char(trunc(sysdate))) - 1, 
                                                            last_day(to_char(trunc(sysdate)))
                                                    )
                                            )-1 ) --substract -1 to get previous working day
                                        - 2   --substract -2 to get second last working day
                                        FROM fnd_cal_day
                                        WHERE trunc(report_date) = trunc(sysdate)
                                    )
                            ELSE (
                                    SELECT trunc(
                                                    decode( to_char(last_day(sysdate), 'D'), 
                                                            '1', last_day(to_char(trunc(sysdate))) - 2, 
                                                            '7', last_day(to_char(trunc(sysdate))) - 1, 
                                                            last_day(to_char(trunc(sysdate)))
                                                    )
                                                ) 
                                    FROM fnd_cal_day
                                    WHERE trunc(report_date) = trunc(sysdate)
                                )
                            END
                -- IF last day is not public holiday
                ELSE (
                            --Get last working
                            SELECT
                               trunc(
                                   decode(  to_char(last_day(sysdate), 'D'), --get the last day of the month and then get the day of the week, i.e. 1 to 7
                                            '1', last_day(to_char(trunc(sysdate))) - 2, --If day of week is 1, i.e. Sunday then last day would be obtained by substracting 2
                                            '7', last_day(to_char(trunc(sysdate))) - 1, --If day of week is 7, i.e. Saturday then last day would be obtained by substracting 1
                                            last_day(to_char(trunc(sysdate))))
                               ) 
                            FROM
                               fnd_cal_day 
                            WHERE
                               trunc(report_date) = trunc(sysdate)
                )
                END last_working_day
        FROM dual
    )