Integrated Document Solutions
for Business Applications

Sort Posts

Get Fresh Updates

Home / Blog / Current Page

Oracle EBS Forms Personalization: Calculating an Item Value

Posted on by Brent Lowe

I received the following comment recently on an older Forms Personalization post:

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

First off, I was flattered that anyone reads these ramblings, especially stuff from 2011.  Second I was intrigued as my immediate answer was…

hmmmmm… don’t know….

So I did a little tinkering around with forms personalization and found a solution.  Now the context of the below solution is likely not the same as the what the commenter is looking at, but I think it should still apply to his situation as I’m just looking at 2 fields and performing some arithmetic on it.   For my example, I’m using the standard Purchase Order form.  I want to automatically calculate the following:

PO_LINES.ATTRIBUTE13 = PO_LINES.UNIT_PRICE / PO_LINES.QUANTITY

In this case: Attribute13 = 35/10

 

To make this happen, fire up forms personalization (Help -> Diagnostics -> Custom Code -> Personalization) and first decide on when you want this calculation to happen.  In my case, I just want it to happen every time a new PO_LINE record is navigated to, so I made the Trigger Event be WHEN-NEW-RECORD-INSTANCE and the Trigger Object be PO_LINES:

You can make this be as complicated as you would like, it all depends on when you want the calculation to happen.

Now, the meat of the problem: how to do the actual calculation.  One of the nice things about forms personalization is that you can just use a query to set item values in a form.  This query can be anything (one caveat though… it has to return a charvalue!).  So for our example, a query that would achieve our goal would be:

select po_lines.unit_price / po_lines.quantity from dual

Easy enough right?  Well yes and no.  This IS the query you need, but in order to have Forms Personalization correctly interpret it there are 2 things you need to worry about.

  1. Syntax
  2. It must return a char value -> NO NUMBERS

For the syntax, you just need to know that in order to reference the actual values in the items you are using to calculate your value you need to use the syntax:

$(item.block.item.value}

For example:

select ${item.PO_LINES.UNIT_PRICE.value}/${item.PO_LINES.QUANTITY.value} from dual

For #2, it must return a CHAR.  So use the SQL function TO_CHAR!

select TO_CHAR(${item.PO_LINES.UNIT_PRICE.value}/${item.PO_LINES.QUANTITY.value}) from dual

Now plug it all into the Forms Personalization form… and you’re done.

As you can see from the above, I’m setting PO_LINES.ATTRIBUTE13’s VALUE property to the return of the select statement that we built and voila, you have a calculated field.
Cool, I learned something, here’s hoping this actually answers our commenter’s question!

 

 


Share This



3 Responses to Oracle EBS Forms Personalization: Calculating an Item Value

  1. Sheryl says:

    Hi,

    Interesting article, just what I needed. So I followed your example and created 2 DFF in the Move Order form to take in 2 numbers (values). I needed to multiply the 2 DFF value and populate the result of the multiplication into the quantity field.

    I could get the quantity field populated when I click ‘Apply Now’ at the Form Personalization form. If I were to exit from the Form Personalization form and contiue my entry on the next record on the Move order line, the quantity field would not be populated after I enter value into both my DFF. It would be populated if I go to Form Personalization and click ‘Apply Now’ button.

    Did I missed anything out here?

    Thanks!

    • Brent Lowe says:

      Not exactly sure without seeing what your personalization looks like, but if I had to guess I would say it’s probably related to your Trigger Event since the personalization itself is working when you ‘Apply Now’. You will need to determine WHEN you want your personalization to fire (WHEN-NEW-RECORD-INSTANCE, etc…) and then set the trigger event appropriately.

  2. Sheryl says:

    Will try out the various trigger event to see which will work. Thanks!

Leave a Reply

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