Function To Get Financial Year Of Date

===== Function To Get Financial Year Of Date========


create or replace function finacial_year(p_date date) return varchar2 is
v_financial_year varchar2(10);
begin

SELECT
          CASE
             WHEN TO_CHAR (EXTRACT (MONTH FROM p_date))
                    BETWEEN 1
                        AND 3
                THEN TO_CHAR
                           (EXTRACT (YEAR FROM p_date) - 1
                           )
             WHEN TO_CHAR (EXTRACT (MONTH FROM p_date))
                    BETWEEN 4
                        AND 12
                THEN TO_CHAR (EXTRACT (YEAR FROM p_date))
          END
       || '-'
       || CASE
             WHEN TO_CHAR (EXTRACT (MONTH FROM p_date))
                    BETWEEN 1
                        AND 3
                THEN TO_CHAR
                           (EXTRACT (YEAR FROM p_date)
                           )
             WHEN TO_CHAR (EXTRACT (MONTH FROM p_date))
                    BETWEEN 4
                        AND 12
                THEN TO_CHAR (EXTRACT (YEAR FROM p_date)+1)
          END
          into
          v_financial_year
  FROM dual;
 
  return v_financial_year;
end function finacial_year;


======= Second Query===========


SELECT TO_CHAR (sysdate),
       DECODE (TO_CHAR (sysdate, 'mon'),
               'jan', TO_CHAR (sysdate, 'yyyy')
                -  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'feb', TO_CHAR (sysdate, 'yyyy')
                -  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'mar', TO_CHAR (sysdate, 'yyyy')
                -  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'apr', TO_CHAR (sysdate, 'yyyy')
                +  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'may', TO_CHAR (sysdate, 'yyyy')
                +  1
                || '-'
                || TO_CHAR (sysdate, 'yyyy'),
               'jun', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'jul', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'aug', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'sep', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'oct', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'nov', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1),
               'dec', TO_CHAR (sysdate, 'yyyy')
                || '-'
                || (TO_CHAR (sysdate, 'yyyy') + 1)
              )
  FROM dual