Monday, July 4, 2011

Date to Fiscal Week Oracle Function Musings (52/53 Week Retail Fiscal Calendar)

Create or Replace Function DT2FW
( bar IN Date )
Return Number
IS
numout Number;
/*******************************************************************************
version 0.1 # Date conversion to Fiscal Week Number # Jeff I # 2011-JUL-04
********************************************************************************
Input: date
Ouput: numeric Fiscal Week Number
********************************************************************************
52/53 Week Fiscal Year at Whole Foods Market IT Notes:
* Weeks end on Sunday and Year Ends on the last complete week in September
* Hence, the Fiscal Year ends on the last Sunday of September
* There are 13 Fiscal Periods, generally four weeks long
, weeks sequenced 1-2-3-4
* On a "leap" (53 week) year, the 13th period contains the "extra" week
, weeks sequenced 1-2-3-3-4
********************************************************************************
Code Notes:
To get the Leap Year FP13 week sequence 1-2-3-3-4:
* Use Mod 4 of the Input Date minus the Begining Date of the Fiscal Year
, decode the integer outputs [0-3] to week numbers [1-4]
, but offset the start of the year by "three weeks"
(the Sunday following (Input Date - 22))
, and offset the decode as well
*******************************************************************************/
bar_year Number := Extract( YEAR from bar );
Begin
If trunc( bar ) <= Next_Day( To_Date( bar_year || '-OCT-01', 'YYYY-MON-DD') - (1+7*3), 'SUN')
Then bar_year := bar_year - 1;
End If;
Select decode(
Mod(Floor((Trunc( bar ) - 1 - Next_Day( To_Date( To_Char( bar_year )||'-OCT-01', 'YYYY-MON-DD') - (1+7*3), 'SUN'))/7),4
),2,1,3,2,0,3,1,4,999) Into numout From Dual;
Return numout;
End DT2FW;
/

Alter Session Set NLS_Date_Format='YYYY-MON-DD';
Alter Session Set NLS_Date_Format='YYYY-MM-DD_HH24:MI';
Alter Session Set NLS_Date_Format='DD-MM-YY';

Select DT2FW('2012-OCT-01') FW From Dual;
Select DT2FW('01-10-12') FW From Dual;

/* Financials Fiscal Period Dates */
Select Accounting_Period, DT2FW(Begin_Dt) Week_Beg, DT2FW(End_Dt) Week_End, Begin_Dt, End_Dt From SYSADM.PS_Cal_Detp_Tbl@FSTST.wholefoods.com
Where SetID = 'WHOLE' And Calendar_ID = 'FP' And Fiscal_Year = 2012 Order By 1;

/* A Date Series */
Select ds, dt2fw(ds) fw
From (Select to_date('2012-JUL-01', 'YYYY-MON-DD') + sn as ds
From dual
Model
Dimension By (0 key)
Measures (0 sn)
Rules Iterate (111) (sn [ITERATION_NUMBER] = ITERATION_NUMBER ))
Order By 1;

Drop Function DT2FW
/