blogs.conchango.com

Thanks for the comments.

In answer to the question about fiscal period, the easiest and lowest tech way to do what you're asking would be to 'tweak' the date you're passing into the SQL so that it handles it according to your wishes. So for example if you want you year to start at April but the function assumes the year to start at January then you can shift all your dates 3 months back. The SQL below shows this (run it for an example)

select to_date('20070401','YYYYMMDD')-1 + level as DateRange

, to_char(to_date('20070401','YYYYMMDD')-1 + level, 'mm') "MONTH"

, to_char(add_months(to_date('20070401','YYYYMMDD'),-3)-1 + level, 'mm') "FISCAL MONTH"

, to_char(to_date('20070401','YYYYMMDD')-1 + level, 'month') "LONG MONTH"

, to_char(to_date('20070401','YYYYMMDD')-1 + level, 'mon') "SHORT MONTH"

, to_char(to_date('20070401','YYYYMMDD')-1 + level, 'q') "QUARTER"

, to_char(add_months(to_date('20070401','YYYYMMDD'),-3)-1 + level, 'q') "FISCAL QUARTER"

, to_char(to_date('20070401','YYYYMMDD')-1 + level, 'yyyy') "YEAR"

, to_char(add_months(to_date('20070401','YYYYMMDD'),-3)-1 + level, 'yyyy') "FISCAL YEAR"

from dual

where (to_date('20070401','YYYYMMDD')-1+level) <= trunc(sysdate)

connect by level<=1000

;

Change the date to '20070101' to see further how this works.

This will get you what you want simply by tricking the server into thinking your at a different point in the year, however you could also have the server configured so that the year starts at April 1st or whatever date applies to your business needs. This of course depends on who else is using that server, and apart from knowing that you can do it I'm afraid I can't help you with this specifically as it's outside my level of experience. I did find this link that may be able to help with this more sophisticated approach http://oraclesvca2.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm

Hope this helps and thanks again for the comments, I appreciate the feedback.

James