I'll give you a steer as to how I'd tackle it.
You have a business day that's 10 hours long, and a 5-day business week. You're given a number of hours to add. So you can calculate three values from that:
weeks := TRUNC(hours/50); -- number of whole weeks
days := TRUNC(MOD(hours,50)/10); -- number of days in the addition to the above
hours := MOD(hours,10); -- Number of hours in addition to the above
First, check that your start date/time falls within a business day. If it doesn't, change it to be the beginning of the next business day.
Then, add (weeks * 7).
Then add days. If the result puts you in or over the weekend, you'll need to add extra days to compensate.
Then add hours/24. If the result puts you outside the business day, you'll need to add extra hours to compensate.
That should get you started.
You said in your original question that you'd be doing the calculation without holidays.
If you need to include provision for holidays in your calculation then yes, you'll need a table to store when the holidays are - there's no magic Oracle function that can tell whether a given day is a holiday at your place of work. You'll also have to add extra steps to ensure that the start day isn't a holiday, and to add days to compensate for any holidays that come up in the task period.
As Christ said, there's no nuilt-in Oracle function to tell whether a given DATE is a holiday or not, partially because there's so much local variation in holidays. You can write a function like that (see http://forums.oracle.com/forums/message.jspa?messageID=3351081 ), but creating a table will be simpler and more efficient.
I suggest creating a row for every date, whether it's a work day or not; that way, you can have variations in the schedule (e.g., schedule changes and
CREATE TABLE work_calendar
( dt DATE PRIMARY KEY
CHECK (dt = TRUNC (dt))
, day_type VARCHAR2 (8) NOT NULL
CHECK ( day_type IN ( 'HOLIDAY'
, 'WORK DAY'
, start_time DATE
, end_time DATE
, work_hours NUMBER
, work_hours_since_1970 -- or some point earlier than you'll ever need
, CONSTRAINT work_calendar_start_time
CHECK ( TRUNC (start_time) = dt)
, CONSTRAINT work_calendar_end_time
CHECK ( TRUNC (end_time) = dt)
This will let you do a lot of the calculations you need without a function.