Customers often have their business requirements for the fiscal calendar not corresponding to the ordinary calendar that starts in January and ends in December. An example is a customer whose financial year is from April 2019 to March 2020 calendar year. The fiscal year for this financial year is 2020. In SEI, the fiscal year by default coinsides with the ordinary calendar. To change this so that the correct fiscal year 2020 is reflected in reports requires changing certain parameters in certain views in the UDM module. These are the UDM_X3CALENDARFINANCE
Below is an example customer reqiurement for a fiscal year 2020, starting from April 2019 to March 31 2020.
|
Customer Period Start Date |
Customer Period End Date |
Default SEI Fiscal Year (UDM) |
Default SEI Fiscal Period (UDM) |
Customer SEI Calendar Year (UDM) |
Default SEI Calendar Month (UDM) |
Expected Fiscal Year |
|
2019/04/01 |
2019/04/30 |
2019 |
1 |
2019 |
4 |
2020 |
|
2019/05/01 |
2019/05/30 |
2019 |
2 |
2019 |
5 |
2020 |
|
2019/06/01 |
2019/06/30 |
2019 |
3 |
2019 |
6 |
2020 |
|
2019/07/01 |
2019/07/30 |
2019 |
4 |
2019 |
7 |
2020 |
|
2019/08/01 |
2019/08/30 |
2019 |
5 |
2019 |
8 |
2020 |
|
2019/09/01 |
2019/09/30 |
2019 |
6 |
2019 |
9 |
2020 |
|
2019/10/01 |
2019/10/30 |
2019 |
7 |
2019 |
10 |
2020 |
|
2019/11/01 |
2019/11/30 |
2019 |
8 |
2019 |
11 |
2020 |
|
2019/12/01 |
2019/12/30 |
2019 |
9 |
2019 |
12 |
2020 |
|
2020/01/01 |
2020/01/30 |
2019 |
10 |
2020 |
1 |
2020 |
|
2020/02/01 |
2020/02/28 |
2019 |
11 |
2020 |
2 |
2020 |
|
2020/03/01 |
2020/03/28 |
2019 |
12 |
2020 |
3 |
2020 |
In SEI, the Fiscal Year is in the UDM_FINANCE table. If a query is run in the UDM_FINANCE table the result is as below showing the Fiscal Year as 2019. But the customer wants this to be 2020.
For the UDM Finance cube, the calculation for the fiscal year is done in the data source of the UDM Finance Staging cube. To do changes, the cube has to be removed from production, and saved first. Go to the OLAP Manager and select the UDM Fianace Staging cube.
Click on > Navigation, then click on > Data Sources.
Then:
(1) Select one Data Source, for example Sage X3 This Year Transactions. .
(2) Click on > Data Source.
(3) Click on Edit Data Sources.
Locate the Fiscal Year field on the following screen and right Click on it and select Edit Field
This will give you access to the script that determine the Fiscal Year. This is what needs to be changed to adjust the Fiscal year to your needs. You can copy the script and analyze it to adjust it you your needs. For our example the Fiscal Year starts on April 1, we will need to change the sections where <=6 is specified to use instead <=4. You will need to do your own tests to identify the right changes to be done for your particular requirements.
Once done with this data source, you need to edit the other Data Sources of this UDM Finance Staging to verify and apply the same changes. there too.
Save and go all the way back to the begining and set the Finance staging cube to Production.
Then one last step.
We need to do changes to the UDM_X3CALENDAR view. It is located in the
Create the view to a new query window.
The section that needs to be modified is boxed below and the changes done similar to what was done on the Fiance staging cube.
Not all views can be updated, and so you will need to remove the old view and re-create it with your modified script.
The Fincnace staging and Fiance cube will have to be rebuilt and reloaded after all these changes.
On querying the UDM_FINANCE view, we see the Fiscal year is now 2020 as per our requirements
The challenge is only in understanding the calculation logic on the scripts and how to insert your own conditions to obtain your requirements.