Integrated Document Solutions
for Business Applications

New Blog Posts

  • November 19th 2014 Fragmented vs. standardized: email and fax delivery in Oracle EBS Read More
  • October 27th 2014 3 ways you can achieve better asset reliabilty Read More
  • October 1st 2014 Simplify shop packet creation in JDE Manufacturing Read More
  • September 23rd 2014 Oracle Open World sessions we are excited about Read More
Home / Blog / Current Page

Forms Personalization: How To Pass Function Parameters

Posted on by Brent Lowe

To continue with my last post on adding a new icon to the menu bar, the ultimate goal of my personalization was to launch a form with dynamic parameters that are named. Launching a form is no problem with Forms Personalization, you simply have a ‘Built-In’ action with the type ‘Launch a Function’.

Forms Personalization: Launch a Function

Knowing what to put in the parameter field is where I got a little lost. I needed to pass through multiple NAMED parameters consisting of information from the form itself (block.item) and SQL Statements. For example:

PARAM1=<value1> PARAM2=<value2>
Where:
value1 is a field on the current form I was personalizing
value2 is the result of a SQL statement

The documentation for Forms Personalization did not really have too much meat as to how to do this, so through some trial and error, the documentation, some forum postings and a My Oracle Support note (429604.1)… I found the following to be true.

1. You can pass form field values as named parameters using the following syntax:
‘PARAM1=’ || ${item.<block>.<item>.value} || ‘ PARAM2=’ || ${item.<block>.<item>.value}

2. Additionally, you can pass form field values as named parameters using the following syntax:
=’PARAM1=’ || :block.item || ‘ PARAM2=’ || :block.item

3. You can pass the value returned from a select statement as a named parameter using the following syntax:
=select ‘PARAM1=’ || <column> A from <table> where <x> = <:block.item>

4. And finally you can combine 2 & 3 above to pass both form field values and the result of a SQL statement at the same time using the following syntax:
==select ‘PARAM1=’ || <:block.item> || ‘ PARAM2=’ || <column> A from <table> where x = &lt:block.item>

Hope this helps!

Other content you may find interesting:

 


Share this Article


10 Responses to Forms Personalization: How To Pass Function Parameters

  1. michael says:

    many thanks to your blog!

  2. Varaprasad Akula says:

    WIll it take the null values for the parameter .. ???

    • Brent Lowe says:

      May need some more information here in order to answer appropriately. If you do not specify a value for a parameter then the form will just use the default value.

  3. Avi says:

    Hi

    I need to launch Ar Transaction form from Order line. I have this Sql to make the link between the Order line and the AR Transaction. The destination Function is “Transactions View”

    What is the format i need to put in the Parameters ? I have this select:

    = ‘fp_customer_trx_id = SELECT rca.customer_trx_id
    FROM oe_order_lines_all oola
    ,ra_customer_trx_lines_all rctl
    ,ra_customer_trx_all rca
    Where 1=1
    And rctl.Interface_line_attribute6=:LINE.LINE_ID
    And rca.customer_trx_id =rctl.customer_trx_id’

    But it does not work

    • Brent Lowe says:

      Hi Avi, if you are trying to populate the parameter fp_customer_trx_id from a select statement, try the method in option 3 of the post:

      select ‘fp_customer_trx_id=’ || rca.customer_trx_id a from …..

      If your query is correct, this will produce a string that ends up looking like this
      fp_customer_trx_id=

  4. coba says:

    Great post, really helpful. Many thanks for sharing your knowledge.

  5. ahmed says:

    how can i get result from the following equation through personalization
    K_LINES.LINE_VALUE = K_HEADER.K_VALUE/K_HEADER.PRIME_K_NUMBER

    i want to know what is the syntax and what is the required steps to get the result automatically

    thanx

  6. Thomas says:

    I am using your notes on this set of parameters and they are working great till I added a parameter where the values have spaces in them. The parameter comes across with the first part of the data up until the spaces and therefore the form fails because it doesn’t find this data from the validation query I have on the form.

    I tried by passing as a parameter

    =’P_CONTRACT_ID=’ || :CHARGES_DETAIL_BLK.CONTRACT_ID || ‘ P_CONTRACT_NUM=’ || :CHARGES_DETAIL_BLK.CONTRACT_NUMBER||’ P_CALLING_REQ=CHARGE’||’ P_QTY=’||:CHARGES_DETAIL_BLK.QTY||’ P_EVENT_TYPE=’||:CHARGES_DETAIL_BLK.SERVICE_ACTIVITY

    I tried by passing in the actual text value

    =’P_CONTRACT_ID=’ || :CHARGES_DETAIL_BLK.CONTRACT_ID || ‘ P_CONTRACT_NUM=’ || :CHARGES_DETAIL_BLK.CONTRACT_NUMBER||’ P_CALLING_REQ=CHARGE’||’ P_QTY=’||:CHARGES_DETAIL_BLK.QTY||’ P_EVENT_TYPE=DTOC Exchange Ship’

    I tried using what I found on spaces

    =’P_CONTRACT_ID=’ || :CHARGES_DETAIL_BLK.CONTRACT_ID || ‘ P_CONTRACT_NUM=’ || :CHARGES_DETAIL_BLK.CONTRACT_NUMBER||’ P_CALLING_REQ=CHARGE’||’ P_QTY=’||:CHARGES_DETAIL_BLK.QTY||’ P_EVENT_TYPE=’||\”:CHARGES_DETAIL_BLK.SERVICE_ACTIVITY\”

    Double Quotes (Wasn’t sure if it was single quotes or double quotes.
    =’P_CONTRACT_ID=’ || :CHARGES_DETAIL_BLK.CONTRACT_ID || ‘ P_CONTRACT_NUM=’ || :CHARGES_DETAIL_BLK.CONTRACT_NUMBER||’ P_CALLING_REQ=CHARGE’||’ P_QTY=’||:CHARGES_DETAIL_BLK.QTY||’ P_EVENT_TYPE=’||\”:CHARGES_DETAIL_BLK.SERVICE_ACTIVITY\”

    Still only passes DTOC.

    Any help would be appreciated.
    Thanks,
    Thomas

    • Brent Lowe says:

      Hi Thomas, thanks for reading.

      We had the exact same problem with spaces. In the end we were unable to find an ‘Oracle’ way to do this so we made up our own. Because the personalization that we were setting up was to invoke a form that WE wrote we had some leeway. Essentially what we did was that we replace the spaces when passing in the parameters with %20. In the form, we take the parameters and replace %20 with space resulting in the orginal value. We chose %20 because it is the URL Encoding value for a space and we were confident it would not already exist in our incoming data.

      For example:

      P_EVENT_TYPE=DTOC%20Exchange%20Ship

      In order to make this dynamic, you will likely have to use the SELECT syntax to set the parameters and use the SQL REPLACE function to replace spaces with %20 or whatever character(s) you choose to use. Of course, this solution all depends if you have control over the form you are launching from the personalization.

      Hope this helps. If you do find a Oracle way to pass spaces that does work, please do share.

      Brent

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>