Hello,
Oracle Retail fans. In this article, we are going to talk all about RMS Calendar
Maintenance. Please note that this article is a sub-article of a complete
article on RMS Oracle Retail Merchandising Systems - Deep Dive.
In RMS, normally, at the end of each period, the system should be reviewed for potential changes or additions to the calendar and half tables. Minor maintenance responsibilities involve adding rows to the calendar table for future periods. It is recommended that the calendar hold at least two years of history details and four years of future dates. The RMS system also bases the financial calendar on the half table. With each half year, the half table requires entries that correspond to the next four half year periods within the next two financial years.
Major changes to the calendar include business changes to the fiscal calendar or changes to the merchandise calendar. Changing the flow of the merchandise calendar from a 4-5-4 to a 4-4-5 or a 5-4-4 is significant because the entire calendar will require restructuring. Because this change will affect year to year comparison, business users would have to be aware of this before restructuring the calendar. It may also be a business requirement to run a custom utility to reset the stock ledger and all sales history tables to match the new merchandise calendar. Changing history data would then allow like comparisons between this year and last year.
An additional merchandise calendar anomaly occurs every 7 years for 4-5-4 calendars, where an extra week must be accounted for when building the weekly calendar structure. Two possible approaches are:
1. Add a single stand alone week period. This is not recommended and requires some modifications to the system.
2. Set up a quarter with the month configuration as 4-5-5 or equivalent 14 week quarter. This is the recommended approach. NOTE: A month cannot be set up to have six weeks in it. This will cause problems in the stock ledger roll-up programs.
4-5-4 Calendar
The 4-5-4 calendar determines the begin point of each financial month within the system, as well as how many weeks it runs for, but does not determine when the financial year ends. The 4-5-4 calendar is determined solely by the table CALENDAR. This table comes pre-populated by the RMS with an EXAMPLE 454 calendar that requires changes to match the required merchandise calendar for the business users.
In order to set the calendar table, enter SQL*Plus and delete the contents of the table. Then either via a script containing insert statements, or by typing in the insert statements at the SQL*Plus prompt, populate the table with your 454 calendar. Date Errors will most likely occur if you enter in a date that is not within these ranges. Error messages will typically appear, such as Calendar table out of Range. Contact the Computer Division.
The Calendar table contains the following columns:
· First_Day: The first day of the 454 month.
· Month_454: The number ranging from 1-12 that indicates the 4-5-4 month. Regardless of when the month starts, it is the month for which it is applicable relative to the calendar month. For example, 454 month 2 may physically start in January, but it will always equate to February. 454 month 7 may physically start in June, but it will always equate to July.
If you are in doubt, add 15 days to first_day, look at what physical month the new date is in, and that is the month_454.
If you are in doubt, add 15 days to first_day, look at what physical month the new date is in, and that is the month_454.
· Year_454: The year that the 454 month falls in, no matter when the month begins.
· No_Of_Weeks: The number of weeks in the 454 month.
To verify the contents in Calendar, run the following command in SQL*Plus:select substr(to_char(first_day,'DD-MON-YYYY'),1,11) "First Day", substr(to_char(first_day,'Day'),1,9) "Day", substr(year_454,1,4) y454, substr(to_char(first_day + 15,'YYYY'),1,9) "Should be", substr(month_454,1,4) m454, substr(to_number(to_char(first_day + 15,'MM')),1,9) "Should be", substr(no_of_weeks,1,5) weeks, substr(to_char(first_day+(no_of_weeks*7), 'DD-MON-YYYY'),1,11) "Next Month" from CALENDAR order by FIRST_DAY/
First Day
|
Day
|
Y454
|
Should be
|
M454
|
Should be
|
WEEKS
|
Next Month
|
24-NOV-1997
|
Monday
|
1997
|
1997
|
12
|
12
|
5
|
29-DEC-1997
|
29-DEC-1997
|
Monday
|
1998
|
1998
|
1
|
1
|
4
|
26-JAN-1998
|
26-JAN-1998
|
Monday
|
1998
|
1998
|
2
|
2
|
4
|
23-FEB-1998
|
23-FEB-1998
|
Monday
|
1998
|
1998
|
3
|
3
|
5
|
30-MAR-1998
|
30-MAR-1998
|
Monday
|
1998
|
1998
|
4
|
4
|
4
|
27-APR-1998
|
27-APR-1998
|
Monday
|
1998
|
1998
|
5
|
5
|
4
|
25-MAY-1998
|
25-MAY-1998
|
Monday
|
1998
|
1998
|
6
|
6
|
5
|
29-JUN-1998
|
29-JUN-1998
|
Monday
|
1998
|
1998
|
7
|
7
|
4
|
27-JUL-1998
|
27-JUL-1998
|
Monday
|
1998
|
1998
|
8
|
8
|
4
|
24-AUG-1998
|
24-AUG-1998
|
Monday
|
1998
|
1998
|
9
|
9
|
5
|
28-SEP-1998
|
28-SEP-1998
|
Monday
|
1998
|
1998
|
10
|
10
|
4
|
26-OCT-1998
|
26-OCT-1998
|
Monday
|
1998
|
1998
|
11
|
11
|
4
|
23-NOV-1998
|
23-NOV-1998
|
Monday
|
1998
|
1998
|
12
|
12
|
5
|
28-DEC-1998
|
28-DEC-1998
|
Monday
|
1999
|
1999
|
1
|
1
|
4
|
25-JAN-1999
|
25-JAN-1999
|
Monday
|
1999
|
1999
|
2
|
2
|
4
|
22-FEB-1999
|
22-FEB-1999
|
Monday
|
1999
|
1999
|
3
|
3
|
5
|
29-MAR-1999
|
29-MAR-1999
|
Monday
|
1999
|
1999
|
4
|
4
|
4
|
26-APR-1999
|
26-APR-1999
|
Monday
|
1999
|
1999
|
5
|
5
|
4
|
24-MAY-1999
|
24-MAY-1999
|
Monday
|
1999
|
1999
|
6
|
6
|
5
|
28-JUN-1999
|
28-JUN-1999
|
Monday
|
1999
|
1999
|
7
|
7
|
4
|
26-JUL-1999
|
26-JUL-1999
|
Monday
|
1999
|
1999
|
8
|
8
|
4
|
23-AUG-1999
|
23-AUG-1999
|
Monday
|
1999
|
1999
|
9
|
9
|
5
|
27-SEP-1999
|
27-SEP-1999
|
Monday
|
1999
|
1999
|
10
|
10
|
4
|
25-OCT-1999
|
25-OCT-1999
|
Monday
|
1999
|
1999
|
11
|
11
|
4
|
22-NOV-1999
|
22-NOV-1999
|
Monday
|
1999
|
1999
|
12
|
12
|
5
|
27-DEC-1999
|
27-DEC-1999
|
Monday
|
2000
|
2000
|
1
|
1
|
4
|
24-JAN-2000
|
Day: Should always be the same day (regardless of whether your first day is a Sunday, Monday, Saturday or whatever), it should simply always be the one day.
Y454: The year that year_454 is currently set to. Should be: The year that year_454 should be set to.
M454 : The month that month_454 is currently set to. Should be: The month that month_454 should be set to.
Looking at the first record, the first day is starting on 24-Nov-97, and the month_454 = 12, even though it does start in November. Remember, if you add 15 days to First_Day for that row, you get 9-Dec-97, and then look at the month, it is December, which is physical month 12 of the year.
Looking at the record that has year_454 = 2000 and month_454 = 1, note that its first_day = 27-DEC-99. So even though the year begins in 1999, and the month begins in December, the values of year_454 and month_454 are for January, 2000.
Next Month: The First Day for the next month.
This verifies that the dates entered will be Year 2000 compliant. If they are not, see the Oracle System Administrators Guide about setting the NLS_DATE_FORMAT, or verify the scripts populating CALENDAR are doing so correctly.
NOTE - There is no mention of Financial Year at all here. The only link at this stage to Financial Year is that the first day of a month within Financial Year and a 454 Month is the same day, and they run for the same number of weeks.
Financial Calendar
The Financial Calendar is determined by one field, System_Options.Start_Of_Half_Month.
This value indicates when the financial year begins for the system.
For example:
If the financial year 1997 begins in July 1997, then the value of start_of_half_month = 7
If the financial year 1997 begins in July 1997, then the value of start_of_half_month = 7
If the financial year 1997 begins in July 1996, then the value of start_of_half_month = -7
If the financial year 1997 begins in February 1997, then the value of start_of_half_month = 2
If the financial year 1997 begins in August 1997, then the value of start_of_half_month = 8
If the financial year 1997 begins in December 1996, then the value of start_of_half_month = -12
As you can see, the value of start_of_half_month equates to the actual Gregorian month. If the financial year begins in the previous year, the month is set to a negative value.
The values of 0 and -1 are not acceptable for start_of_half_month. The reason is simple, there is no month 0, and month -1 would imply that an entire year belongs to the following year. For example, if financial year 1996 begins in January 1995, the whole year of 1996 is contained within 1995. This would not make sense.
This is the normal calendar, with each month beginning on the 1st of the month. The Gregorian calendar is used within the RMS mainly when retrieving historical data.
Typically, it is then converted into either a 4-5-4 format, or a financial half and month.
HALF Table
The HALF table requires the same time frame of information in it that is in the CALENDAR table. Therefore, if the CALENDAR table contains data to the year 2010, then the HALF table should also contain data to this date.
This is due to the Date routines that are in the Batch Programs and Oracle Stored Procedures cross referencing these calendars against one another.
PERIOD Table
Once the above tables have been initialized, the process of populating the PERIOD tables is quite straightforward.
Go to the UNIX prompt and execute the following RMS program from the command line (make sure that the $PATH variables contains the directory for the program, or that you are in the RMS $MMHOME/oracle/proc/bin directory).
Unix> dtesys Oracle_Userid/Oracle_Password YYYYMMDD
Where YYYYMMDD is the date that you wish to initialize the system to.
For additional information on the columns within the PERIOD table, refer to the RMS Data Dictionary.
SYSTEM_VARIABLES Table
The SYSTEM_VARIABLES table is the table which holds information about the last closed week and month, and the next week and month periods to be closed. A week is closed when the SALWEEK.PC batch program is run, a month is closed when the SALMTH.PC batch program is run.
The following script will correctly populate the SYSTEM_VARIABLES table. This script must only be run after the CALENDAR and PERIOD tables have been correctly populated.
update SYSTEM_VARIABLES set ( LAST_EOM_HALF_NO , LAST_EOM_MONTH_NO , LAST_EOM_DATE , NEXT_EOM_DATE , LAST_EOM_START_HALF , LAST_EOM_END_HALF , LAST_EOM_START_MONTH , LAST_EOM_MID_MONTH , LAST_EOM_NEXT_HALF_NO , LAST_EOM_DAY , LAST_EOM_WEEK , LAST_EOM_MONTH , LAST_EOM_YEAR , LAST_EOM_WEEK_IN_HALF , LAST_EOW_DATE ) = ( select p.half_no - decode( p.curr_454_month_in_half, 1, decode( mod(p.half_no,10), 1, 9, 1), 0 ) LAST_EOM_HALF_NO, decode( p.curr_454_month_in_half, 1, 6, p.curr_454_month_in_half - 1) LAST_EOM_MONTH_NO, p.start_454_month - 1 LAST_EOM_DATE, p.end_454_month NEXT_EOM_DATE, add_months( p.start_454_half, decode( p.curr_454_month_in_half, 1, -6, 0)) LAST_EOM_START_HALF, add_months( p.end_454_half, decode( p.curr_454_month_in_half, 1, -6, 0)) LAST_EOM_END_HALF, add_months( p.start_454_month, -1) LAST_EOM_START_MONTH, add_months( p.start_454_month, -1) + 14 LAST_EOM_MID_MONTH, p.half_no + decode( p.curr_454_month_in_half, 1, 0, decode( mod(p.half_no,10), 1,1, 9)) LAST_EOM_NEXT_HALF_NO, decode( to_char(p.start_454_month-1,'DD'), '28', 7, mod( to_char(p.start_454_month-1,'DD'),28) ) LAST_EOM_DAY, decode( to_char(p.start_454_month-1,'DD'), '28', 4, 5) LAST_EOM_WEEK, to_char(p.start_454_month-1,'MM') LAST_EOM_MONTH, to_char(p.start_454_month-1,'YYYY') LAST_EOM_YEAR, ceil( ( p.start_454_month - 1 - add_months( p.start_454_half, decode( p.curr_454_month, 1, -6, 0)) )/7 ) LAST_EOM_WEEK_IN_HALF, p.start_454_month - 1 + trunc(to_char(p.vdate,'DD')/7)*7 LAST_EOW_DATE from PERIOD p ) /update SYSTEM_VARIABLES set LAST_EOM_DATE_UNIT = LAST_EOM_DATE, NEXT_EOM_DATE_UNIT = NEXT_EOM_DATE, LAST_EOW_DATE_UNIT = LAST_EOW_DATE, NEXT_EOW_DATE_UNIT = LAST_EOW_DATE + 7 /
About Nagesh Mishra
Nagesh Mishra - A Passionate Oracle Retail Certified Professional with more than 17 years of overall experience in IT industry and more than 15 years of domain expertise in Oracle Retail Applications. Worked extensively in diversified fields of Product Implementation, Business Consulting, Pre-Sales, Application Software Development, Maintenance and Support and Re-Engineering Oracle Retail projects.
0 comments :
Post a Comment