Fiscal Calendar 5-4-4 creation question
I wanted to create a custom financial year and the following are the details :
First Quarter
Jan(01) 05
Feb(02) 04
Mar(03) 04
Second Quarter
APR(04) 05
May(05) 04
June(06) 04
Third Quarter
July(07) 05
Aug(08) 04
Sep(09) 04
Fourth Quarter
Oct(10) 05
Nov(11) 04
Dec(12) 04
The procedure should update a calendar table with the following logic :
Calendar_date - Actual Date should add 1 for every insert
Calendar_Year - YYYY of sysdate
Calendar_month - MM of sysdate
Fiscal_Year - Fiscal_year being passed into the procedure.
Fiscal_Quarter - Should be calculated based on the information from the above Fiscal_Quarter information. Should increase by 1 with change in each quarter.
Fiscal_Week - For every 7 days the fiscal_week should increase by 1.
Day in Week - 7 for sunday, 1 for monday, 2 for tuesday et al
week_age - should be set to max(week_age)+1 with change in every week or for every 7 days.
Period_Age - should be set max(period_age)+1 with change in every month.
Quarter_age - should be set to max(quarter_age)+1 with change in every quarter.
If anyone has been through the above experience, can that be shared. Also what kind of design would be the best. I can have a reference table for the number of weeks for information on how many weeks constitute each fiscal month. What will be the best approach, if anyone can please suggest. Is there a reusable script for such needs anywhere?
Thanks in Advance,
Ravi
Cheers,
Ravi