I guess the rest should be obvious, apart from the decision how to fill this table (I use 100 years and supply a button in the application that allows to add/edit rows for the future
here is a 'trick' which will return the correct fiscal year, without the need to add a new table to do the conversion.
what you want to do is to ADD 3 months to the actual date, and then take the Year of the result (the three months will effectively advancce OCTOBER of the current year to JANUARY of the FOLLOWING year, and the YEAR function will then return the 'correct Fiscal Year', as the Year to which the follong January belongs). You do this only to get the Fiscal Year of the data, not to actually modify the dates in any way.
Select <Date_Field>,TO_CHAR(Add_Months(<DATE_Field>, 3), 'YYYY') as Fiscal_Year from YourTable
Change <Date_Field> to the name of the Date field in your table.
so, to be portable, you have only 1 solution:
create a table with 3 (4) columns
* start_date
* end_date
* fiscal_year_display
* key_field
I guess the rest should be obvious, apart from the decision how to fill this table (I use 100 years and supply a button in the application that allows to add/edit rows for the future