My question is how do I manipulate the procedure to calculate cut-off date with in working days and it should skip weekends and holidays?
I have holiday table to determine holidays
Table Name : HOLIDAYMSTR
HOLIDAYMSTR_ID RAW(16)
PREV_UPDATED_BY RAW(16) Y
LAST_UPDATED_BY RAW(16)
HOLIDAY_DATE DATE
EFFECTIVE_DATE DATE
EXPIRY_DATE DATE Y
PREV_UPDATED_DATETIME DATE Y
LAST_UPDATED_DATETIME DATE SYSDATE
DEFUNCT_IND CHAR(1) 'N'
COUNTRY_CODE VARCHAR2(10) Y
HOLIDAY_DESC VARCHAR2(100)
HOLIDAY_DESC_LANG1 VARCHAR2(100) Y
HOLIDAY_DESC_LANG2 VARCHAR2(100) Y
HOLIDAY_DESC_LANG3 VARCHAR2(100) Y
For example:
If the date is 01/01/2007 and the resulting date should be return 02/01/2007.
procedure get_LastCutOffDt(v_date_fr in date,
v_date_rtn out date,
v_Error_rec out AHH.AHH_ERRLOG_MSG%rowtype)
is
v_days_rtn varchar2(15);
begin
--logic to get last cut off date based on the working days setup
v_error_rec.error_code := 0;
v_error_rec.error_text := '';
return;
exception
when others then
rollback;
v_error_rec.error_code := 9999;
v_error_rec.error_text := substr(sqlerrm,1,100);
dbms_output.put_line('0.1 '||v_error_rec.error_text);
ahh_pkg_error.ins_ERRMSG_LOG(v_error_rec);
return;
end get_LastCutOffDt;
The logic should check holidays and weekends and produced the last cut-off date. I shall be very greatful, if you could kindly help on this.