Calendar LOV for date parameters in Concurrent Programs – A workaround
September 5, 2011
Posted by on
Metalink Note#457838.1 – How to Enable the Calendar LOV For a Concurrent Program Parameter clearly mentions the reasons why it is not possible to associate a Calendar LOV for the Parameter Form in the SRS Window. However, if you talk to the end-users about this, they will tell you that this single feature, if available, would have altered their lives forever 🙂 Too bad for Oracle – there were quite a few lives that could have been altered there!
Coming back to the question at hand, one of the workarounds is to provide a list of dates from which the user can choose instead of having to type in the date manually. Such a list can be generated by using an SQL statement similar to the one below which fetches a list containing dates from the past one year
select to_date(sysdate+1-level,'DD-MON-RRRR') p_date from dual connect by level<365;
Let us consider a practical scenario. The user needs to submit a concurrent program which has a date parameter and we need to ensure that he can only enter dates which fall between seven days FROM today and seven days BEFORE today. Here is how we do it(assuming that the concurrent program has already been registered):
1. Create a value set(XXX_TEST_SET) of Format Type:Standard Date and Validation Type: Table
2. In the Validation Table Information window, enter the following in the Table Name field. Specify the column alias which will provide the value for the valueset. The datatype and size will always be ‘Date’ and ’11’ respectively.
(select to_date(sysdate-1+level,'DD-MON-RRRR') p_date from dual connect by level<=8
select to_date(sysdate+1-level,'DD-MON-RRRR') p_date from dual connect by level<=8)
3. Use this value set(XXX_TEST_SET) for the Concurrent Program’s date parameter.
When the use submits the Concurrent Program, he will have to select a date from within the displayed ones. The workaround will definitely not win awards for elegance but at least the user can select dates from a list instead of entering them manually. Also it provides a way of restricting the dates which the user can enter.