Oracle ERP R12 – 客製Fucntion-取得某年某月第N週第N日的日期

因最近發現許多報表會要求欄位名稱依月份的週別來顯示,並顯示該週的起迄日,

故寫一FUNCTION供其它開發人員調用.

就上述需求,則由下FUNCTION中輸入年/月/週 及1 和 7,就可取得該週第1天及第7天的日期.

====================================================================

CREATE OR REPLACE FUNCTION XX_GET_MONTH_WEEK_DAY(I_YEAR IN NUMBER,

                                                  I_MONTH IN NUMBER,

                                                  I_WEEK IN NUMBER,

                                                  I_DAY_NO IN NUMBER) RETURN DATE

IS

  /*

  When     Who       What

  =================================

  20110811 XXX   Created for 取得某年某月某週之第n天的日期

  */

  T_RESULT DATE;

  T_DATE   DATE := TO_DATE(TRIM(TO_CHAR(I_YEAR,'0000'))||

                           TRIM(TO_CHAR(I_MONTH,'00'))||

                           '01','YYYYMMDD');

  T_MONTH_START_DATE NUMBER; 

  T_DAY              VARCHAR2(2);                          

BEGIN

  SELECT TO_CHAR(TRUNC(T_DATE,'MM'),'D')

    INTO T_MONTH_START_DATE

    FROM DUAL;

  --

  SELECT T_DATE+

         DECODE(TO_NUMBER((I_WEEK-1)*7 + I_DAY_NO - T_MONTH_START_DATE),

                -1,0,

                TO_NUMBER((I_WEEK-1)*7 + I_DAY_NO - T_MONTH_START_DATE))

    INTO T_RESULT

    FROM DUAL;

  --

  RETURN(T_RESULT);

END XX_GET_MONTH_WEEK_DAY;