create or replace function TF_ISSUE_DATE(ISSUE IN VARCHAR2,DATE_TYPE IN VARCHAR2) return DATE  --參數DATE_TYPE 返回日期類型  0起始日期 1中止日期
is
  V_RETURN DATE;
  V_ISSUE VARCHAR2(20) :=ISSUE;
  V_DATE_TYPE VARCHAR2(20) := DATE_TYPE;
  V_ISSUE_TYPE VARCHAR2(20) :=SUBSTR(ISSUE,1,1);
begin
  IF V_DATE_TYPE = '0' THEN
    CASE
    WHEN V_ISSUE_TYPE = '1'  －－年
    THEN V_RETURN := TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD');
         
    WHEN V_ISSUE_TYPE = '2'  －－季
    THEN SELECT DECODE( SUBSTR(V_ISSUE,6,2),
                        '01',TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD'),
                        '02',TO_DATE(SUBSTR(V_ISSUE,2,4)||'0401','YYYY-MM-DD'),
                        '03',TO_DATE(SUBSTR(V_ISSUE,2,4)||'0701','YYYY-MM-DD'),
                        '04',TO_DATE(SUBSTR(V_ISSUE,2,4)||'1001','YYYY-MM-DD'),TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD'))
                INTO V_RETURN
          FROM DUAL;
         
    WHEN V_ISSUE_TYPE = '3' －－月
    THEN V_RETURN := TO_DATE(SUBSTR(V_ISSUE,2,6)||'01','YYYY-MM-DD');
         
    WHEN V_ISSUE_TYPE = '4' －－旬
    THEN SELECT AA INTO V_RETURN FROM (
            SELECT aa ,ROWNUM BB
            FROM (
                 SELECT (TO_DATE (SUBSTR(V_ISSUE,2,6)||'01', 'yyyy-mm-dd') + ROWNUM - 1) aa  ,rownum cc
                 FROM all_objects
                 WHERE ROWNUM < TO_DATE(TO_CHAR(last_day(TO_DATE (SUBSTR(V_ISSUE,2,6)||'01', 'yyyy-mm-dd')),'YYYYMMDD'),'YYYY-MM-DD') - TO_DATE (SUBSTR(V_ISSUE,2,6)||'01', 'yyyy-mm-dd') + 1) bb 
            WHERE MOD (TO_CHAR (aa, 'dd'), 10) = 1 and to_char(aa,'dd') <> 31 )
          WHERE BB = TO_NUMBER(SUBSTR(V_ISSUE,8,2));
         
    WHEN V_ISSUE_TYPE = '5' －－周
    THEN SELECT MONDAY INTO V_RETURN FROM (
            select
            MONDAY.the_week,decode(sign(MONDAY.the_day-SUNDAY.the_day),-1,MONDAY.the_day,MONDAY.the_day-7) MONDAY,SUNDAY.the_day SUNDAY
            from
                 (select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm the_day
                  from (
                       select trunc(TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD'), 'MM')+rownum-1 as wwm
                       from user_objects
                       where rownum < 366
                       )
                  where to_char(wwm,'D')=2 ) MONDAY,
                 (select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm the_day 
                  from (
                        select trunc(TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD'), 'MM')+rownum-1 as wwm
                        from user_objects
                        where rownum < 366
                       )
                  where to_char(wwm,'D')=1 ) SUNDAY
            where MONDAY.the_week=SUNDAY.the_week)
         WHERE THE_WEEK = SUBSTR(V_ISSUE,6,2);
         
    WHEN V_ISSUE_TYPE = '6' －－日
    THEN V_RETURN := TO_DATE(SUBSTR(V_ISSUE,2,8),'YYYY-MM-DD');
         
    ELSE
        V_RETURN := TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD');
    END CASE;
  ELSE
    CASE
    WHEN V_ISSUE_TYPE = '1'
    THEN V_RETURN := TO_DATE(SUBSTR(V_ISSUE,2,4)||'1231','YYYY-MM-DD');
         
    WHEN V_ISSUE_TYPE = '2'
    THEN SELECT DECODE( SUBSTR(V_ISSUE,6,2),
                        '01',TO_DATE(SUBSTR(V_ISSUE,2,4)||'0331','YYYY-MM-DD'),
                        '02',TO_DATE(SUBSTR(V_ISSUE,2,4)||'0630','YYYY-MM-DD'),
                        '03',TO_DATE(SUBSTR(V_ISSUE,2,4)||'0930','YYYY-MM-DD'),
                        '04',TO_DATE(SUBSTR(V_ISSUE,2,4)||'1231','YYYY-MM-DD'),
                        TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD'))
                INTO V_RETURN
          FROM DUAL;
         
    WHEN V_ISSUE_TYPE = '3'
    THEN V_RETURN := TO_DATE(TO_CHAR(last_day(TO_DATE(SUBSTR(V_ISSUE,2,6)||'01','YYYY-MM-DD')),'YYYYMMDD'),'YYYY-MM-DD');
         
    WHEN V_ISSUE_TYPE = '4'
    THEN SELECT DECODE( SUBSTR(V_ISSUE,8,2),
                        '01',TO_DATE(SUBSTR(V_ISSUE,2,6)||'10','YYYY-MM-DD'),
                        '02',TO_DATE(SUBSTR(V_ISSUE,2,6)||'20','YYYY-MM-DD'),
                        '03',TO_DATE(TO_CHAR(last_day(TO_DATE(SUBSTR(V_ISSUE,2,6)||'01','YYYY-MM-DD')),'YYYYMMDD'),'YYYY-MM-DD'),
                        TO_DATE(TO_CHAR(last_day(TO_DATE(SUBSTR(V_ISSUE,2,6)||'01','YYYY-MM-DD')),'YYYYMMDD'),'YYYY-MM-DD'))
                INTO V_RETURN
          FROM DUAL;
         
    WHEN V_ISSUE_TYPE = '5'
    THEN SELECT SUNDAY INTO V_RETURN FROM (
            select
            MONDAY.the_week,decode(sign(MONDAY.the_day-SUNDAY.the_day),-1,MONDAY.the_day,MONDAY.the_day-7) MONDAY,SUNDAY.the_day SUNDAY
            from
                 (select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm the_day
                  from (
                       select trunc(TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD'), 'MM')+rownum-1 as wwm
                       from user_objects
                       where rownum < 366
                       )
                  where to_char(wwm,'D')=2 ) MONDAY,
                 (select to_char(wwm,'WW') the_week,to_char(wwm,'D') the_daynum,wwm the_day 
                  from (
                        select trunc(TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD'), 'MM')+rownum-1 as wwm
                        from user_objects
                        where rownum < 366
                       )
                  where to_char(wwm,'D')=1 ) SUNDAY
            where MONDAY.the_week=SUNDAY.the_week)
         WHERE THE_WEEK = SUBSTR(V_ISSUE,6,2);
         
    WHEN V_ISSUE_TYPE = '6'
    THEN V_RETURN := TO_DATE(SUBSTR(V_ISSUE,2,8),'YYYY-MM-DD');
         
    ELSE
        V_RETURN := TO_DATE(SUBSTR(V_ISSUE,2,4)||'0101','YYYY-MM-DD');
    END CASE;
  END IF;
  return(V_RETURN);
end TF_ISSUE_DATE;