fiscal year mm

select

TO_CHAR(ADD_MONTHS(TO_DATE(CONCAT(&P_FROM_DATE,'01'), 'yyyymmdd'),6),'YYYYMM') 

from dual;

-----------------

CREATE OR REPLACE FUNCTION fiscal_month_year( period IN varchar2 ) RETURN VARCHAR2 IS

    fiscal_month_year VARCHAR2(20);

/******************************************************************************

   NAME:       fiscal_month_year

   PURPOSE:    

   REVISIONS:

   Ver        Date        Author           Description

   ---------  ----------  ---------------  ------------------------------------

   1.0        5/15/2023   Md. Quium Hossain       1. Created this function.

   NOTES:

   Automatically available Auto Replace Keywords:

      Object Name:     fiscal_month_year

      Sysdate:         5/15/2023

      Date and Time:   5/15/2023, 1:03:33 PM, and 5/15/2023 1:03:33 PM

      Username:        Md. Quium Hossain (set in TOAD Options, Procedure Editor)

      Table Name:       (set in the "New PL/SQL Object" dialog)

******************************************************************************/

   

BEGIN

fiscal_month_year := 0;

  IF SUBSTR(period, 5, 2) = '01' THEN

    fiscal_month_year := CONCAT('Jul-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '02' THEN

    fiscal_month_year := CONCAT('Aug-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '03' THEN

    fiscal_month_year := CONCAT('Sep-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '04' THEN

    fiscal_month_year := CONCAT('Oct-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '05' THEN

    fiscal_month_year := CONCAT('Nov-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '06' THEN

    fiscal_month_year := CONCAT('Dec-', TO_CHAR(SUBSTR(period, 1, 4)));

  ELSIF SUBSTR(period, 5, 2) = '07' THEN

    fiscal_month_year := CONCAT('Jan-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '08' THEN

    fiscal_month_year := CONCAT('Feb-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '09' THEN

    fiscal_month_year := CONCAT('Mar-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '10' THEN

    fiscal_month_year := CONCAT('Apr-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '11' THEN

    fiscal_month_year := CONCAT('May-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  ELSIF SUBSTR(period, 5, 2) = '12' THEN

    fiscal_month_year := CONCAT('Jun-', TO_CHAR(SUBSTR(period, 1, 4) + 1));

  END IF;

   RETURN fiscal_month_year;

END fiscal_month_year;

/