Oracle EBS 根据工作日历取工作日

/*=====================================================
  根据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;