/*===================================================== 根据NLT工作日历 如果p_due_date 是工作日， 返回p_due_date 如果p_due_date 不是工作日，取得 p_due_date 之前最近的一个工作日，返回 Add by xiaoiqng.zhang@2012-12-05 =====================================================*/ FUNCTION get_pre_work_day(p_due_date IN DATE) RETURN DATE IS l_api_name CONSTANT VARCHAR2(30) := 'GET_PRE_WORK_DAY'; l_moudle CONSTANT VARCHAR2(100) := g_pkg_name || '.' || l_api_name || ' : '; l_fix_due_date DATE; --向前推至最近的一个“工作日”，即支付开始日 l_calendar_code VARCHAR2(100) := 'CAL_FIN'; l_due_date DATE; --待定支付开始日 BEGIN l_due_date := p_due_date; SELECT MAX(bcd.calendar_date) INTO l_fix_due_date FROM bom_calendar_dates bcd WHERE bcd.calendar_code = l_calendar_code AND NOT EXISTS (SELECT 'holiday', bce.exception_date FROM bom_calendar_exceptions bce WHERE bce.calendar_code = l_calendar_code AND bce.exception_date = bcd.calendar_date) AND bcd.calendar_date <= l_due_date AND bcd.calendar_date > l_due_date - 15 AND to_char(bcd.calendar_date, 'D') NOT IN ('1', '7'); dbms_output.put_line(to_char(l_fix_due_date, 'YYYY-MM-DD')); RETURN l_fix_due_date; EXCEPTION WHEN no_data_found THEN dbms_output.put_line(l_moudle || ' can not found!'); cux_avic_conc_utl.log_msg(l_moudle || ' can not found!'); RAISE fnd_api.g_exc_error; WHEN too_many_rows THEN dbms_output.put_line(l_moudle || ' too many rows'); cux_avic_conc_utl.log_msg(l_moudle || ' too many rows'); RAISE fnd_api.g_exc_error; WHEN OTHERS THEN dbms_output.put_line(l_moudle || 'unexception error occured!'); cux_avic_conc_utl.log_msg(l_moudle || 'unexception error occured!'); RAISE fnd_api.g_exc_error; END;
取日期之后最近的工作日
/*===================================================== 根据NLT工作日历 如果p_due_date 是工作日， 返回p_due_date 如果p_due_date 不是工作日，取得 p_due_date 之后最近的一个工作日，返回 Add by xiaoiqng.zhang@2012-12-05 =====================================================*/ FUNCTION get_after_work_day(p_due_date IN DATE) RETURN DATE IS l_api_name CONSTANT VARCHAR2(30) := 'GET_AFTER_WORK_DAY'; l_moudle CONSTANT VARCHAR2(100) := g_pkg_name || '.' || l_api_name || ' : '; l_fix_due_date DATE; --向前推至最近的一个“工作日”，即支付开始日 l_calendar_code VARCHAR2(100) := 'CAL_FIN'; l_due_date DATE; --待定支付开始日 BEGIN l_due_date := p_due_date; SELECT MIN(bcd.calendar_date) INTO l_fix_due_date FROM bom_calendar_dates bcd WHERE bcd.calendar_code = l_calendar_code AND NOT EXISTS (SELECT 'holiday', bce.exception_date FROM bom_calendar_exceptions bce WHERE bce.calendar_code = l_calendar_code AND bce.exception_date = bcd.calendar_date) AND bcd.calendar_date >= l_due_date AND bcd.calendar_date < l_due_date + 15 AND to_char(bcd.calendar_date, 'D') NOT IN ('1', '7'); dbms_output.put_line(to_char(l_fix_due_date, 'YYYY-MM-DD')); RETURN l_fix_due_date; EXCEPTION WHEN no_data_found THEN dbms_output.put_line(l_moudle || ' can not found!'); cux_avic_conc_utl.log_msg(l_moudle || ' can not found!'); RAISE fnd_api.g_exc_error; WHEN too_many_rows THEN dbms_output.put_line(l_moudle || ' too many rows'); cux_avic_conc_utl.log_msg(l_moudle || ' too many rows'); RAISE fnd_api.g_exc_error; WHEN OTHERS THEN dbms_output.put_line(l_moudle || 'unexception error occured!'); cux_avic_conc_utl.log_msg(l_moudle || 'unexception error occured!'); RAISE fnd_api.g_exc_error; END;