While doing implementation of oracle application in one of my project we came across a requirement for testing ahead a year and a quarter. The requirements were to process transactions in future/past and complete the year end closing. As we know Oracle applications are getting their ‘current date’ from sysdate and then do processing. Even though most of the financial closing can be managed through GL Date but some of our scenarios was to validate against the system date. What we were looking at that time to find some solution and easy way to change the date rather then changing the server date. As by changing the server date we were impacting the other applications that were residing on the same server. Then we came across with this very little known parameter called FIXED_DATE
As per Oracle FIXED_DATE enables you to set a constant date that SYSDATE will always return instead of the current date. To undo a fixed date setting, specify FIXED_DATE=NONE.
This parameter is useful primarily for testing. The value can be in the format shown above or in the default Oracle date format, without a time. Setting this parameter to a specified timestamp will make the time constant for the database engine (the clock will not tick) FIXED_DATE is a dynamic parameter and can be changed using the ALTER SYSTEM command.
How to set FIXED_DATE :-
ALTER SYSTEM SET FIXED_DATE=’2010-01-30-00:00:00′;
SQL> select sysdate from dual;
SYSDATE
———
30-Jan-10
Note: The format can be as shown above or the oracle default date format.
How to unset:-
ALTER SYSTEM SET FIXED_DATE=NONE;
SQL> select sysdate from dual;
SYSDATE
———
07-Jan-11
Setting this parameter does not impact systimestamp and you can always see the server time, you can use systimestamp. This is usually used for testing/development purposes, when the application logic depends on a specific date/time combination.
Few points to consider while enabling this feature for Oracle application Testing:
- Don’t go in the future as some of the business process does not allow dates in future. We had issues in receiving (sub ledger) as receiving is not allowed in future due to audit reasons and due to that we were not able to test the Express receiving in iprocurement
- Requisition Need by dates are not allowed in past but we had workaround of changing the need by date inPO
- Alerts which are defined in Alert Manager and need to be tested with time could not be tested
- Workflow background process didn’t behave correctly as it was not able to pick the activity with correct time. We had issue in closing our sales orders as they were depending on workflow background process.
This parameter did help us in testing future and in past but we had our own share of issues also for application testing. So this is recommended to do complete due diligence for using this parameter for oracle application testing.
appsfromrajiv 