How to use lexical parameters in oracle reports

how to use lexical parameters in oracle reports

How to Use Global Variables in Form Personalization

Use Global Variables in Form Personalization:

Here let’s say the requirement is to run a concurrent program through a custom menu from an oracle form and in that concurrent program parameters, we need to fetch some values from the oracle form itself. In that case we need to first assign the form values to some Global Variables and then use them in the concurrent program parameters. Here are the Steps:

1] Go to Form Personalization for that Form (Use: Help > Diagnostics > Custom Code > Personalize)

  • Select Type as ‘Property’ and select Object Type as ‘Global Variable’.
  • Give a name to the global variable in the ‘Target Object’ tab. (Ex. G_ITEM_NAME, G_ORG_CODE)
  • Select the Property Name as ‘VALUE’.
  • In the value tab- Give ‘=:Block_name.Field_name’ (Use: Help > Diagnostics > Examine). This is the value which you want to put in the global variable.

4] Select Type as ‘Builtin’ and Select Builtin Type as ‘Launch SRS Form’.

5] Select your Concurrent Program in the ‘Program Name’ tab. Please note that you need to assign the Function – ‘Requests: Submit’ to the Main Menu of the responsibility to which your concurrent program is attached.

  • Use: System Administrator > Security > Responsibility > Define to find the Menu Name.
  • Go to System Administrator > Application > Menu and add the Function – ‘Requests: Submit’ at the end and Save.
  • Also don’t forget to attach the concurrent program to proper Request Group.
6] Go to the concurrent program parameters. Select the Default Type as SQL Statement in the Validation tab and give the default values as
  • select :GLOBAL.G_ITEM_NAME from dual
  • select :GLOBAL.G_ORG_CODE from dual

7] Save and Test the functionality.

An Alternate Way:

Create a PL/SQL function in the database that calls fnd_request.submit_request and commits in an AUTONOMOUS TRANSACTION. The function returns a message to the user, with the request_id.

1] Go to Form Personalization for that Form (Use: Help > Diagnostics > Custom Code > Personalize)

2] Create one custom menu (ex. SPECIAL15) on the triggering event: WHEN-NEW-FORM-INSTANCE.

3] On the triggering event: SPECIAL15, do the following actions:

  • Define a global variable for the message (Ex. XX_CONC_PROG_RESULT)
  • Assign the above global variable the following value: =SELECT <Your Custom PL/SQL Function> from dual. You can pass parameters to the function as: Block_name.Field_name.
  • Define a message to show as =:GLOBAL.XX_CONC_PROG_RESULT


Category: Bank

Similar articles: