Friday, December 11, 2015

AOL - Dynamically enabling and disabling Parameters in Oracle Concurrent Program

There is a requirement that dynamically enabling and disabling Concurrent Program Parameters.

Let a concurrent program has two parameters – Display and Application Name. If the value for Display is ‘YES’, then Users should be enabled containing names of Applications in a system and if the value for Display is ‘NO’, then Application Name parameter gets disable.

Initially Application Name parameter is disable.

·   If user has selected YES from Display parameter then Application Name value set enabled containing application names.

·   If user has selected NO from display then Application Name disabled containing application names.

 To create value set:
Navigation: Application developer à Application à Validation à Set
Create an Independent type value set

Create YES and NO values for above XXAA_DISPLAY_PARAMETER valueset
Navigation: Application developer à Application à Validation àValues

Then create one Dummy Set of none type for enable and disable other parameter  
XXAA_NONE_DIPLAY_PARAM value set to enable User Value set

To create Concurrent Executable:
Navigation: Application developer à Concurrent à Executable

To create Concurrent Program:
Navigation: Application developer à Concurrent à Program
Create concurrent program:
Let Executable Name: XXAA_ENABLE_DISABLE_PARAMETER
Method: PL/SQL  Stored Procedure

Click on Parameters.
As first parameter attach XXAA_DISPLAY_PARAMETER value set which provide the options YES or NO

Here we are using Display Parameter Parameter to enable and disable other parameter of concurrent program.
Create a parameter of any name and assign a value set XXAA_NONE_DIPLAY_PARAM of NONE type to it.

Select default type as SQL Statement and in Default type write query as:

Select case when :$FLEX$.XXAA_DISPLAY_PARAMETER ='YES' then 'Y' else null end from dual

When YES means application is selected by display value set then XXAA_NONE_DIPLAY_PARAM value set returns ‘Y’ otherwise NULL.

All the table type value set which satisfies the where condition ‘Y’=:$FLEX$.validation are enable when this query runs and others will remain disable.

Now create value set which should be enable on particular condition:
Navigation: Application developer à Application à Validation à Set
Create Value set XXAA_APPLICATION_NAME of table type:

Write ‘Y’=:$FLEX$.XXAA_NONE_DIPLAY_PARAM in where condition so that when validation return Y this value set get enable otherwise remains disable.

Assign XXAA_APPLICATION_NAME value set to the parameters of concurrent Program and save it.

Attach the concurrent program to Request Group of the responsibility.
Navigation:
System Administrator à Security àResponsibility àRequest

Uncheck the Display check box so that it is hidden from user.



Run the concurrent program by
View à Requests à Submit a new Request àSingle Request.

Output :
Here user selects NO from Display value set then Application Name disabled.

Here user selects YES from options value set then application value set get enabled.


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect