“Form Personalizations” Is Your Friend – Part I
July 21st, 2006 | Send this article to a friend!
So many times throughout my career I’ve wanted to slightly change the way a form behaves but have been held back because it was considered, dare I say it, a customization to the form. Just seeing the word customization sends implementers running, screaming for Larry. I can just see the developers giving me that “crazy” look when I ask for an open text field to be validated. The horror!
But now with the introduction of Form Personalizations with 11.5.10 CU1, I can confidently say to my client “Yes, we can validate this field.” And I can say so without sending any consultants running or getting those crazy looks from the development cube.
Form Personalizations allow you to alter the way objects on the form behave without having to modify any source code. Personalizations allow you to do things such as make fields required that normally aren’t, add additional validation, create new menu options, etc. And if you have a little SQL knowledge, Form Personalizations can become even more powerful.
As a simple example, let’s say you have a requirement to validate the Customer PO field on the sales order form so it always starts with the characters “PO-”. Your first course of action would be to navigate to the Sales Order form.
Next we’ll need to access Form Personalizations. To do this click on Help > Custom Code > Personalize. You’ll be prompted to enter the APPS password. If you established a good relationship with your DBA you should have this.
Once you enter the password you’ll find yourself within the Form Personalizations screen.
Along the top you’ll notice three fields. The function name refers to the function that was used to open the form you’re currently in - in our case the Sales Order form. This can differ based on the way you access the form. For instance, if I accessed the Sales Order form directly from the menu vs. if I clicked the “New Order” button from the Order Organizer initiates two different functions to call the form.
Next is the form name, this refers to the system name of the form your in. The form we’re in is OEXOEORD.
The next block allows you to describe your personalization, assign a sequence, and choose whether you want it to trigger at a form or function level. For example, let’s say you want this personalization to trigger only when the Sales Order form is accessed from the Order Organizer. You would first make sure you navigate to the form using this path, then select “Function” when you create your personalization. But, in our case, our client wants the Customer PO field to be validated no matter how the user accesses the form. So to meet this requirement we will choose “Form”.
Next we will fill in the condition information within the Condition tab. The first field we encounter is the Trigger Event field. This allows us to specify when we want our personalization to trigger. For those who have experience with Forms development this will look very familiar. The following trigger events are available:
• WHEN-NEW-FORM-INSTANCE – initiates your personalization upon entry into the form.
• WHEN-NEW-BLOCK-INSTANCE – initiates upon entry to a block within the form. A block usually is a particular section within the form.
• WHEN-NEW-RECORD-INSTANCE – initiates upon creating a new record within a block.
• WHEN-NEW-ITEM-INSTANCE – initiates when navigating into a particular form object. This can be a text field, radio button, list of values, etc.
• WHEN-VALIDATE-RECORD – initiates when a record is being validated. This usually happens upon saving.
You’ll also notice a slew of other options in the list of values labeled MENU and SPECIAL. These refer to other events which allow you to trigger personalizations upon selecting menu options or other special events specific to the form. You can also type in a trigger event not within the list of values that is specific to the form you’re personalizing. In our case, since we want to validate the record to ensure that Customer PO starts with “PO-“, we’ll use the WHEN-VALIDATE-RECORD event.
The next field is the Trigger Object. This works in conjunction with the Trigger Event field. For instance, if you selected the WHEN-NEW-BLOCK-INSTANCE event, you would select which form block you want your personalization to trigger. And for the WHEN-NEW-ITEM-INSTANCE event, you would select the item. In the case of the WHEN-NEW-FORM-INSTANCE event there’s nothing to select because this event triggers upon entry into the form.
Since we’re using the WHEN-VALIDATE-RECORD event, we’ll need to specify which block to initiate our personalization. You’ll notice that Trigger Object will list all blocks within the form. How do we know which block to choose? If you’re familiar with the Examine option located in Help > Diagnostics, you can use this option to examine the particular block, record, and item within the form and determine which block it is in. In our case, the Customer PO field is located in the ORDER block, so this is the block we’ll specify in this field.
The Condition field - this is where the magic happens. This is where we enter the condition that triggers our personalization and requires a bit of SQL knowledge. In our case we want to ensure the Customer PO number always starts with the “PO-“ characters.
Let’s think through the process – the user goes into the form, enters a correct customer PO number, say “PO-123456”, along with the other validated information, then navigates to the order lines. Do we want an event to trigger in this situation? No, because the user entered a correct Customer PO number.
What about the opposite situation? If the user enters an incorrect Customer PO number, say “123456”, then navigates to the lines block, do we want an event to trigger? Yes, we would want an error message to appear letting the user know they have entered an incorrect Customer PO.
With that said, we want our Condition to check the Customer PO to see if it doesn’t start with “PO-“.
So how did I know that the Customer PO field was :ORDER.CUST_PO_NUMBER? There are two ways to find this out. You can use the Examine option and examine the actual field in the form, or you can use the “Insert Item Value…” button located on the bottom of the Form Personalizations form. Using this button allows you to navigate all items located in the form. This option is good if the item name is identical or similar to its label, but sometimes this isn’t the case.
The next field, Processing Mode, gives you the option to trigger your personalization while the user is in insert mode, query mode, or both. In our case, we’ll select “Not in Enter-Query Mode”, since we only want to validate the Customer PO field if a new order is being entered.
As we navigate into the Actions tab we see an assortment of different options.
The first block we see contains the Sequence, Type, Description, and Language fields. If we wanted to trigger multiple actions, the Sequence field could be used to specify the order in which we want our actions to occur.
The Type field specifies what type of action to trigger. This field provides us with four options:
• Property – This particular action allows us to change a property of a form object.
• Message – Provides us the ability to display a warning or error message.
• Builtin – Allows us to execute a procedure within the form or stored in the database.
• Menu – Allows us to add or change menu options in the form.
Since our condition checks to see if the Customer PO field doesn’t properly begin with “PO-“, we want a Message type so that we can display a warning. You’ll notice that the block on the right side of the form will change with different settings.
The Language drop down allows us to perform actions based on the language being used. This is useful if you have multi-lingual capabilities enabled and what to display different translations of an error message. To keep it simple, we’ll initiate our action for all languages by selecting “All”.
When we move to the next block we see the following options:
The Message Type field let’s us specify what kind of message we want to display. Since we want to prompt an error message, we’ll select “Error”.
The Message Text is the text that will display when the message is triggered. Let’s display the text “The Customer PO must start with “PO-”.” To test our message, we can click the Validate button located on the bottom right side of the form.
We’re finished! To confirm that our Form Personalization is correct we must save and validate the entire trigger by going to Tools > Validate All. You should receive a message that says “The Personalizations validated successfully.” If you don’t, go back to your condition and make sure you entered it correctly.
Now let’s test our Personalization! To do this, we must back completely out and navigate back into the form for the Personalizations to take effect. Once you do this, let’s enter a new sales order and purposely enter a Customer PO that doesn’t start with the appropriate characters.
Now that we have all the sales order header information entered, let’s navigate to the Line Items tab.
Well I’ll be darned! I forgot that my Customer PO number has to start with “PO-“. Let me go back and correct it.
Now when I navigate to the Line Items tab, the sales order header saves and let’s me continue with entry of my lines.
There you have it! You just added additional validation without customizing the form and upsetting your fellow colleagues. Who needs a developer anyways? Kidding…
Mr. Thompson is a Senior Oracle Applications Consultant with Lexerd Group Consulting. Visit www.LexerdGroup.com to find out more about our firm.
Interested in hosting your own blog? Lexerd Group’s Internet Services division provides specialized blog hosting services that utilizes the Wordpress blogging platform. Visit http://www.lexerdgroup.com/blog-yourself/ for more details.
Entry Filed under: Technical
39 Comments Add your own
1. atlurisunil | August 2nd, 2006 at 3:32 pm
Bryan.
Did you ever run a database procedure using Forms Personalization. I am having a hard time running a procedure for FP. Can you please help me out.
Thanks
Sunil
2. bryan | August 2nd, 2006 at 4:00 pm
Yes, under the Actions tab. If you change the Type to “Builtin”, then select “Execute a Procedure” in the “Builtin Type” list of values you’ll be able to enter the procedure call within the Argument field.
Hope this helps…
3. Anonymous | September 8th, 2006 at 4:54 pm
I’m trying to run a builtin in the actions tab, specifically….execute procedure. I put the procedure call into the arguments box, but unless I hardcode all the values…the procedure just errors out. It won’t recognize and variables that I have made reference to within the argument box. If I do an apply now, it says variables are not bound…acts like the variables don’t even exist. I make reference to both global variables that I have set and to field on the block level…neither are recognized. What am I doing wrong? I’m having trouible finding any documentation on forms personalization that even touches on this subject. Can you help me out please and thanks.
4. bryan | September 8th, 2006 at 4:55 pm
To my knowledge - there is no ability reference global or form variables within the builtin arguments. I\’ve also tried this in the past and have had no luck.
If you create a function wrapper for the procedure, you can incorporate the procedure call into a select statement that be placed in the value box for setting a form object property. The SQL would look like this in the value box…
=select function_name(:block.var1, :block.var2) A from dual
Hope this helps…
5. Anonymous | September 8th, 2006 at 4:57 pm
Thanks Bryan
I’ll have to give that a shot.
I actually found and got some variables to work with the built-in. I like your solution better though as this one still has some glitches it seems.
Its really odd syntax but the following does work too:
In the argument box type your procedure call using the following as an
example:
For globals its:
=’begin
test_procedure(”’||${item.global.field.value}||”’);
end’
for fields on block its:
=’begin
test_procedure(”’||${item.block.field.value}||”’);
end’
6. Anonymous | September 8th, 2006 at 4:58 pm
Actually, your solution is the better one, but in my case I can’t use it as my procedure takes a lot of variables and the “value box” it seems is limited to the amount of text that goes in there.
7. bryan | September 8th, 2006 at 4:59 pm
Interesting, this could come in handy.
I’ve also had problems with text limitation in the value box. Usually if it’s something on the hefty side I’ve had to create a concurrent process that runs in short intervals.
Thanks for providing these examples.
8. Anonymous | September 8th, 2006 at 4:59 pm
Documentation on this stuff is sketchy at best so go for it, and hopefully it’ll help some others in the same boat.
I’ve noticed with the builtin that what values the variable is substituting will affect the success of the builtin call as well…..for example if a variable can be NULL, it could cause problems because the string that is being put together in the argument box may become invalid due to the syntax
at runtime. You basically have to make sure that if you where to
substitute the variables into the argument…would it be a valid syntax correct script that you could run (containing the necessary quotes , brackets, etc…).
9. Anonymous | September 11th, 2006 at 4:15 pm
Hi Bryan
In forms personalization when working with built-ins such as “execute procedure” or messages, I just thought I’d let you know that I successfully got both references to items from the form and global variables working in the stuff I was working on.
1) for fields from form use the following syntax: ${item.block.field.value}
2) for global variables use the syntax: :global.field
Example#1: if you were working with an action of type “Message” and wanted to throw out a message when a field changes on the form, and you have the original value stored in a global variable, then the message body for this would look like the following:
‘The field value changed from ‘||:GLOBAL.field||’ to ‘|| ${item.block.field.value}
Example#2: if you were working with an action of type “Builtin” and where using “execute procedure” to invoke a procedure on the database and needed to pass in the old and new value from the Example#1 then the argument box syntax is as follows:
=
‘begin
test_procedure(”’||:GLOBAL.field||”’,”’||${item.block.field.value}||”’);
end’
Hope this helps you or any others that may be working on forms personalization.
10. thomas | February 20th, 2007 at 2:48 pm
Bryan,
Would you know if the personalization part of the code is executed before or after the trigger (used in our triger event ) is executed.
For instance if I assument that my trigger event is WHEN-NEW-RECORD-INSTANCE of a block, and I am setting certain values, which are also getting affected in the WHEN-NEW-RECORD-INSTANCE trigger on the block in the underlying FMB. So in this case whose changes will persist. The ones I made in my Personalization, or the values set in the form ?
Regards
Thomas
11. bryan | February 20th, 2007 at 3:09 pm
Thomas,
That\’s a good question - a question I don\’t have the answer to. :-) My advice would be to try and avoid this kind of situation. But to find out which trigger would ultimately affect your values, I would place an alert message in the WHEN-NEW-RECORD-INSTANCE trigger of the FMB and also in the WHEN-NEW-RECORD-INSTANCE event of the personalization. Whatever message triggers last would be the one that prevails.
Bryan
12. ankur_aneja | April 17th, 2007 at 4:47 am
Hi Bryan,
I am facing a similar situation like one of your examples using when-validate-record trigger. I have to restrict the users from creating a record in the Master supplier form if they are trying to create a record for Affilaite Supplier.
The following are the setups which I have done.
I have a requirement for which I have to restrict he user from creating a record in the Supplier Master form if the suppliier type is ‘ GE Affiliate Supplier’.
I have done the following setups
Seq 10
Description Restricting user from creating Affiliate records
Level Function
Enabled Yes
Condition:
Trigger Event WHEN-VALIDATE-RECORD
Trigger object VNDR
Condition “${item.VNDR.VENDOR_TYPE_DISP_MIR.value} is NOT NULL
and
${item.VNDR.VENDOR_TYPE_DISP_MIR.value} LIKE ‘Affiliate%’
Processing Mode NOT IN ENTER QUERY MODE
Context
Level User
Value User Name
1
Action Sequence 1
Type Message
Action Description Saving Affiliate record
Language ALL
Message Type Show
Message Text You Cannot Create Affiliate records Here
Action Sequence 2
Type Builtin
Action Description Stop Proceesing
Language ALL
Action Enabled Yes
Builtin Type RAISE FORM_TRIGGER_FAILURE;
The problem here is that I have applied the when-validate-record trigger to the VNDR block but it is firing even when I query for an affiliate record and navigate to the VNDR_USES block in the same form.
Any suggestions on this would be higly appriciated.
Thanks in Advance.
13. oteixeira | May 11th, 2007 at 7:41 am
Hello Bryan
I’m release 11.5.10.2 of Oracle EBS and trying to personalize form ARXRWMAI (Enter Receipts). I’m trying to obtain a message to the user every time he saves a new receipt. These are the values I entered to achieve this:
So for sequence 1 at level Function:
Condition
Trigger Event: WHEN-VALIDATE-RECORD
Trigger Object: RGW_FOLDER
Condition: None (Evaluates to true)
Processing Mode: Not in Enter_Query Mode
Context: Site
Actions:
Seq: 1
Type: Message
Language:ALL
Message Type: Show
Message Text: =’Receipt Nr: ‘||:RGW_FOLDER.DOCUMENT_NUMBER
When I test this personalization I do not get the expected results. When I save a new receipt the message “Receipt Nr:” is displayed but without the value of the corresponding global variable DOCUMENT_NUMBER. Also I have to press 2 time the OK message button for it to disappear. What can I be doing wrong?
Any help on this issue is highly appreciated.
Octavio
14. lmeijaard | June 13th, 2007 at 5:23 am
I’m struggling with the call to a database procedure with a parameter (invoice_id) through forms personalizations. I found a couple of examples at this site but still not manage to get it working.
This is the code in the argument.
begin
xx_lvnl_invappr_wf.abort_previous_flow_fp(:INV_SUM_FOLDER.INVOICE_ID);
end
I’ve tested all you’re examples but it is still not working.
Can someone help me out?
Kind regards Leo Meijaard
15. KRISHNA | September 25th, 2007 at 6:42 am
Hi Bryan,
How do I copy personalizations from one instance to another instance, without recreating them manually??
Thanks for the advise,
Regards
Krishna Chaitanya P
16. Nilesh | October 31st, 2007 at 8:33 am
Hi,
Iam facing the :block.field reference error in copy and mass change functionality in sales order form, can somebody help. Iam on 5.10.2 with ATG RUP5
thanks
Nilesh
17. Joe Padanilam | November 19th, 2007 at 8:48 am
Hi, Bryan!
Is it possible to have a form personalization perform a mouse-click on a button?
Thanks,
Joe
18. krish | December 20th, 2007 at 10:03 pm
I am trying to call a function from another form using Built In ‘Lanch a function’.
In Add parameters, If I provide hardcoded values, its working fine. When I try to pass variables such as :GLOBAL.XX_ITEM_ID or ${item.folder.item_id.value} ,
it errors out
When I use GLOBAL variable, the error is ‘Type does not match definition in form XXX ‘ I tried using to_char , to_number but no use.
When I use item.folder.item_id.value it errors out with ‘FROM keyword not found’
I tried putting them in single quotes, pipes etc.
Can anybody tell me how to pass variabes while launching a function?
Thanks a lot.
19. krish | December 21st, 2007 at 9:57 am
I got it…used the following syntax
‘G_QUERY_FIND=PO_LINE_LOCATION_ID
PO_LINE_LOCATION_ID=’||${global.xx_po_line_location_id.value}
20. bryan | December 21st, 2007 at 12:25 pm
Krish - thanks for sharing this information! A lot of us were trying to figure out how to reference form field values for the longest time. Glad someone was able to figure it out!
Bryan
21. Raj | January 15th, 2008 at 3:21 pm
Hi Bryan,
I need to call a database procedure which has 2 parameters.. The second parameter is an OUT parameter which returns a value.. Is there a way of storing this returned value into a global or local variable and show it in the application as a message ? Can you please help me out ?
Appreciate your help.
Thanks!
Raj
22. Silpa | February 7th, 2008 at 8:53 am
Hi Bryan
I need to call a procedure with an out parameter from forms personalization.I also need to reference the value of this out paramater.
Can you tell me how to do this?
Also above krish has mentioned that he is using built in launch function. In this can we use a custom defined function in the database or do we need to choose only from the LOV?
Appreciate your help.
Thanks
Silpa
23. Linda | February 21st, 2008 at 11:53 am
I’m trying to get a forms personalization working using a builtin and I’m getting an error and I’m not sure what I’m doing wrong.
Trigger Event: When new item instance
Trigger Object: site.vendor_site_code
Condition: Null
Seq: 10
Type: Builtin
Builtin Type: Execute a Procedure
Argument: =’begin
CBAP_PO_VENDOR_TRIG_PKG.ADD_IP_VENDOR(”’||${item.VNDR.VENDOR_NAME.value}||”’,”’||${item.SITE.VENDOR_SITE_CODE.value}||”’,”’||${item.SITE.VENDOR_ID.value}||”’);
end’
When I press Apply I get the following error:
The String=’begin
CBAP_PO_VENDOR_TRIG_PKG.ADD_IP_VENDOR(”’||${item.VNDR.VENDOR_NAME.value}||”’,”’||${item.SITE.VENDOR_SITE_CODE.value}||”’,”’||${item.SITE.VENDOR_ID.value}||”’);
end’ could not be evaluated because of error ORA-00923: FROM keyword not found where expected.
Do you have any advice on how I can get this working?
Thanks,
Linda
24. Linda | February 22nd, 2008 at 1:21 pm
Please disregard my request for help. I got it working with the following syntax:
=’begin
cbap_po_vendor_trig_pkg.add_ip_vendor(”’||${item.vndr.vendor_name.value}||”’,”’||${item.site.vendor_site_code.value}||”’,”’||${item.site.vendor_id.value}||”’,”’||${item.vndr.vendor_type_lookup_code.value}||”’);
end’
Linda
25. Neha Raghuwanshi | March 10th, 2008 at 4:53 am
HI Bryan,
I am trying to restrict particular responsibilities to use the “Release Sales order form” for releasing orders.
But they should be able to release orders using only 2 out of all the release rules visible in the “based on rule” LOV.
Now I want to modify the LOV to show only these two release rules to the desired responsibilities.
Can this be achieved through form personalization?
Please reply soon as we need to communicate regarding the feasibility of this to the client ASAP.
Regards,
Neha
26. Reedy | April 1st, 2008 at 3:25 pm
Bryan:
I am facing the same situation as in the posting
oteixeira | May 11th, 2007 at 7:41 am
where I am not able to capture the document_number.
Any help on this is greatly appreciated.
Thanks.
Reedy
27. Gopi | June 3rd, 2008 at 8:18 am
Hi Raj,
Did you get any solution for your query : “Having Global Variable as output parameter of a Procedure and then using that in a message” ?
28. Shankar | June 25th, 2008 at 2:00 am
Hi Bryan,
I am trying to run a report from a Menu entry passing some parameters. I can get it working as far as the menu entry invokes the SRS form. But the 3 parameters appear concatenated in the first parameter field itself.
I believe the values in the global variable need to be seperated by a period(.) and work_orders.parameter seperates them automatically in ther SRS ?
Also, my report has a few hidden parameters, do I need to pass a value for them as well or simply the displayed parameters.
an example would really help.
Many Thanks
Shankar
29. sean mark | June 30th, 2008 at 8:43 pm
Hi Bryan,
can i use the output of the stored procedure in forms personalization to be an indicator to create a message?
for example in builtin>execute procedure I have an output variable which returns BOOLEAN. If true then there will be a message stating that an error occured and if it is false then there should be no message.
Is that possible?
please advise.
thank you so much..
Sean
30. bryan | July 1st, 2008 at 7:10 am
Sean, off hand I’m not sure if this is possible. However, if you can somehow set a form variable with the results of your procedure, you could create another personalization which evaluates the form variable, and if true, sets off a message action.
Give this a shot and let me know if it works.
Bryan
31. sean mark | July 2nd, 2008 at 12:30 am
Hi Bryan,
‘ve encountered another problem.. The procedure that I use in forms is using a DML. thus creating an error message ORA-14551 : cannot perform a DML operation inside a query.. What I did is I create another procedure for the DML statement but still same error appeared.
can you give some workaround and tips in solving this error?
thank you so much.
Sean
32. pankaj | July 25th, 2008 at 2:26 am
My requirement is , on transaction form(AR), I want to make Invoicing Rule mandatoy if user selects
Trx_type as ‘INVUSMAINT’ (predefined hardcoded value), it could have been easily achieved by
using new_item_instance on any/all of the Customer name/number fields to make invoicing rule mandatory for above value in trx type.
BUT hin ourcase, user do not follow the sequence while entering values on this form
, He puts customer information first and at the last he selects trx type and then save the records, this way logic written in any of the new item instance will NOT work.
So now I am using WHEN-VALIDATE-TRIGGER on TGW_HEADER, but here the issue is whenever I press SAVE button, it makes the invoicing rule mandatory but it validates all other fields and gives pop-up for address field whenever it finds
multiple sites (though they have different bill-to location, addess1 to address3 are same).
So can you suggest me how can I supress(restrict) it not to validate other fields on form?
Thanks
Pankaj
33. Kamlesh | September 16th, 2008 at 12:38 am
Hi, I am doing personalization wehere i have written one global variable and want to access in to execute query but its not working please help me!!!!!!!!!!!!!!!!!!!!!
=’declare
XX_temp_var varchar2(1);
begin
XX_TEST_PROC(XX_temp_var);
global.XX_FLAG_CHK_FR_CLMS:=XX_temp_var;
end’
regards,
kamlesh
34. Jay | October 17th, 2008 at 4:11 am
Hi , Am currently doing a For mpersonalization where I have to restrict the Supplier name to have only alphabets or numbers. I have created a function to identify if the string contains anything other than Alphabets or numbers. I am unable to use the function here. Will anybody be able help me with this personalization.
Jay
35. Gershon Hartov | November 10th, 2008 at 4:24 am
hi,
i want to run concurrent from FORM PERSONALIZATION.
i use message and the value is = the function return me message ’success’ or ‘error’.
the function run Fnd_Request.SUBMIT_REQUEST.
can i do it like that?
i need to change the priter in the concurrent without the user noticed .
thanks for you help.
regards,
Gershon Hartov.
36. bryan | November 10th, 2008 at 4:59 pm
Hi Gershon,
It sounds like you’re using a “Property” type action. To execute a procedure, I believe you need to use a “Builtin” type action, where in this action you can specify the PL/SQL package or procedure to execute.
Hope this helps,
Bryan
37. Yogeeta | November 17th, 2008 at 12:37 pm
Hello! Bryan
I would like to get an example on how to write a trigger event that is not on the list.
Can the event be “POST-COMMIT”?
I would like to populate a field on Post Commit
38. Hi Bryan | December 1st, 2008 at 3:16 am
I would like to pass the literal to FND_REQUEST.SUBMIT_REQUEST . How can I achieve this objective?
39. Søren Moss | December 18th, 2008 at 8:33 am
On 11.5.10 we are encurraged to use LOCAL variables instead of GLOBAL variables. Global variables are limited to 255 characters, whereas local variables are limited to 4000.
When using (referencing) a global variable, I can write :GLOBAL.variablename
But how do I use a local variable?
Regards,
Søren Moss
Leave a Comment
Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>
Trackback this post | Subscribe to the comments via RSS Feed | Send To a Friend