Function for fiscal year conversion to calendar year

 

CREATE OR REPLACE FUNCTION x_get_fiscal_to_calendar_date_fun (p_year NUMBER, p_period NUMBER)

RETURN VARCHAR2 IS

   v_date    DATE;

   v_result  VARCHAR2(8);

BEGIN

   -- Calculate the first day of the month based on the year and period

   v_date := ADD_MONTHS(TO_DATE(p_year || '-7', 'YYYY-MM'), p_period - 1);

   

   -- Format the result as YYYY-MON

   v_result := TO_CHAR(v_date, 'YYYY-MM');

   

   RETURN v_result;

EXCEPTION

   WHEN OTHERS THEN

      RETURN NULL; -- Handle exceptions if any

END;

/