This program I'm working on needs to be able to display a warning indicator when a task gets too close to its due date. I allowed the user to specify how many days until due date is safe. The problem is, this needs to take into account weekends and holidays (if respected). So, I needed a way to query what the "warning date" is.

Get Due Date Warning.sql

SELECT MIN(days.SD) 
FROM 
(SELECT SYSDATE + 366 - ROWNUM SD,
	EXTRACT(YEAR FROM SYSDATE + 366 - ROWNUM) Y,
	EXTRACT(MONTH FROM SYSDATE + 366 - ROWNUM) M,
	EXTRACT(DAY FROM SYSDATE + 366 - ROWNUM) D
FROM ALL_OBJECTS WHERE ROWNUM < 368) days
LEFT JOIN WF_NON_WORK_DAYS holidays ON holidays.DAY = days.D AND holidays.MONTH = days.M
WHERE days.SD <= :DUE_DATE AND
	(holidays.NON_WORK_DAY IS NULL OR holidays.NON_WORK_DAY = 0) AND
	(1 + TRUNC(days.SD)  - TRUNC(days.SD, 'IW')) NOT IN (6,7) AND ROWNUM <= :DAYS_UNTIL_DUE