Oracle Apps Notes

A collection of my random notes, primarily on Oracle Apps

Calendar LOV for date parameters in Concurrent Programs – A workaround

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
union
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.

Advertisements

3 responses to “Calendar LOV for date parameters in Concurrent Programs – A workaround

  1. coolsank May 24, 2013 at 4:20 pm

    Can freeze a parameter of a concurrent program …and make it display only ..is it possible
    ?

  2. Arik September 1, 2016 at 3:44 pm

    Hello autor, thanks alot for your good idea.
    I assume the following one will work as well
    select to_date(sysdate-8+level,’DD-MON-RRRR’) p_date from dual connect by level<16

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: