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;
/
