Oracle EBS Forms Personalization: Calculating an Item Value

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!