| Data source for fields in a form [message #547659] |
Thu, 15 March 2012 09:47  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I have a horrible problem with EBS (actually, all problems with EBS are horrible) and I think I am stuck because of my ignorance of Forms. Please excuse me if I use terms that are not correct in the Forms world.
The form consists of a number of named "blocks" and each block consists of a number of named "fields", not all of which are visible. I need to find the source of the data values in one of these fields. I have searched every table for a column of that name, also all the views and stored PL/SQL that I think might be relevant, but I can no find no mention of a column or variable with the same name as the field. The name does not get a hit in the online EBS tech ref manual, and only two ancient and irrelevant hits in MOS.
My question is:
What are the possible sources of data for a field in a form? Have I missed any?
Thanks for any help.
|
|
|
|
| Re: Data source for fields in a form [message #547660 is a reply to message #547659] |
Thu, 15 March 2012 09:53  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
They're probably not based on anything. Relevant properties on the item are Database Item and Column Name.
If Database item is No then they don't correspond to anything in the DB.
If Yes then they correspond to a column in the DB of same name as the item, unless column name is set to something else.
|
|
|
|
|
|
| Re: Data source for fields in a form [message #547662 is a reply to message #547661] |
Thu, 15 March 2012 10:17 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Thank you for replying. EBS does let me look at some properties for the item, there is one called Column Name which is null.
If I understand you correctly, that means that the item might be populated by anonymous PL/SQL stored in some way within the Form? I have the FMB file, would I need to open that with the Forms editor (whatever that is!) to extract the PL/SQL?
|
|
|
|
| Re: Data source for fields in a form [message #547663 is a reply to message #547662] |
Thu, 15 March 2012 10:31 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Trouble here is I have no idea how EBS works, never used oracle apps. The forms app I used to work on was homegrown.
So I'm just going to have to talk about the way forms standardly work.
Forms editor is forms builder. I have no idea whether you can still get it seperatly or if it's bundled with something else. LF probably knows.
Column name of null is fairly normal. It doesn't need to be populated as it defaults to item name.
It's fairly common to have a block based on a table with most of the items in it corresponding to columns in the table (those would be the items with Database Item property set to Yes), then you might have some other items that display information from other tables.
Say there is a location code column. You might want to display the full name of the location along side the code. But the full name is in a different table.
So you add an item for it. Set it's Database Item property to No. Then add some code to the post-query and when-validate-item triggers to populate it. Or you might use an LOV instead of when-validate-item.
|
|
|
|
| Re: Data source for fields in a form [message #547664 is a reply to message #547663] |
Thu, 15 March 2012 10:58 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
OK, perhaps I can get somewhere now. I have the frmbld.sh script which launches the Form Builder (I suspect it would be a bit prettier on Windows) and having located the the fmb I can navigate around and find the block and the item. As you thought, it is Database Item=No and it is associated with some triggers and and a list of values. When I get totally stuck I may be back for more help.
Thanks again.
|
|
|
|
|
|
| Re: Data source for fields in a form [message #547682 is a reply to message #547664] |
Thu, 15 March 2012 13:36 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
John Watson wrote on Thu, 15 March 2012 11:58Item=No and it is associated with some triggers and and a list of values. When I get totally stuck I may be back for more help.
Thanks again.
John, you have a good head start from cookie, but when I have a massive form and need to find an "object" I use a freeware called Form Mate. Type in an object name and it tells you where it is used. The reason to use this over built-in "find and Replace PL/SQL" is that this will also look in LOV's and Canvases, which do not show up in the built-in search.
It is shown as an evaluation copy at http://www.jockvale.ca/downloads.html, but I haven't encountered any restrictions and have been using it for about 2+ years. Now, I only used it a handful of times, so maybe it has a number of times used limit.
|
|
|
|
| Re: Data source for fields in a form [message #547685 is a reply to message #547682] |
Thu, 15 March 2012 13:54 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
I've downloaded it, I'll try it tomorrow. I've found that the form I'm working on includes a couple of dozen packages that have functions invoked by the triggers and so on. This is horrible! I had no idea that there was all this anonymous pl/sql kicking around! And I have to track down items in a lot of forms....
I sure hope Forms Developer is easier to use on a Windows machine. On Linux, it is painful.
|
|
|
|
| Re: Data source for fields in a form [message #547693 is a reply to message #547685] |
Thu, 15 March 2012 15:21 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
John Watson wrote on Thu, 15 March 2012 14:54I've downloaded it, I'll try it tomorrow. I've found that the form I'm working on includes a couple of dozen packages that have functions invoked by the triggers and so on. This is horrible! I had no idea that there was all this anonymous pl/sql kicking around! And I have to track down items in a lot of forms....
I sure hope Forms Developer is easier to use on a Windows machine. On Linux, it is painful.
I tried in on Linux once (because I try to avoid anything Microsoft). The rendering of the form is awful and did not translate will to when we put it up on the Application Server on Sun, so I went back to Windows version.
|
|
|
|
|
|
| Re: Data source for fields in a form [message #547696 is a reply to message #547694] |
Thu, 15 March 2012 15:29 |
owais_baba
Messages: 289
Registered: March 2008
Location: MUSCAT
|
Senior Member |
|
|
some Oracle EBS Tips
Forms Personalization
Today's post is about forms personalization. Forms personalization is a way to customize Oracle Applications without requiring a broad knowledge on programming languages. Although, if you want to perform more complex customizations you might require some PL/SQL skills.
I will try to explain with a very simple example the basic functionality of forms personalization and on the way I will review on how to call a stored procedure within the personalization and how to catch messages from the procedure and print them on screen (you'll be surprised on how useful can this messages be)
As usual, here is the list of what we need, and how we will do it.
We need:
1.- Oracle Applications (obviously)
2.- The Utilities and Diagnostics profile enabled at the user level (you should be careful with this profile, since it enables the examine function for Oracle forms)
3.- A bit of knowledge on Forms Personalization. If you are new to this you can review some information here and here
And this other document contains more detailed information on the topic.
4.- SQL and PL/SQL skills will be very helpful when developing more complex personalizations. If you feel like learning PL/SQL you can download the Oracle PL/SQL bible here
How we will do it:
1.- Clearly define what your personalization needs to accomplish. This can be as simple as setting a field as required or inactivating a field, and as complex as inserting records into an interface table, calling public APIs to perform transactions, launching Oracle preseeded or customized Oracle Workflows.
2.- From Oracle EBS, identify what objects and events you need to use and set up the personalization.
3.- If you are calling a procedure, you might need to catch a message as a result. The easiest way to do this is to create a table and insert the results on this table. This way you will just need to perform a simple SQL statement to get the message.
4.- Incorporate security to your personalization if needed.
5.- Test your personalization.
Ok so let's start.
First thing to do is to setup the Utilities and Diagnostics profile at the user level as Yes. TO do this log into System Administrator responsibility--Profiles. Now query for Utilities:Diagnostics at the user level (be careful with this you should NEVER set it up at the site level since you will grant access to every user which can lead to very harmful results).
Once this profile has been set up log into the Oracle EBS form that you which to customize. Go to Help Diagnostics Custom Code --- Personalize.
This will make the Forms Personalization window to prompt.
Now for the purpose of this example we will enable a menu in the form. This menu will call a procedure in the database. This procedure will perform some actions (this actions can be anything that you want) and return a message to the user. I have put all the figures of this post in a file that you can access here
First let's create the menu. Forms Personalization works with pre-defined events and objects. WHEN-NEW-FORM-INSTANCE event as you can guess is called every time the form is loaded. This event is useful for things that need to be done once (such as adding menus and initialize variables. If you are not sure when an event is called, you can set forms to show all events by going to Help--Diagnostics--Custom Code--Show Custom Events. This will launch a message for every event that is executed.
What we will do is first create a personalization. Input a sequence and a description and set the event to be WHEN-NEW-FORM-INSTANCE. Input any condition for this personalization if you need to, leave blank if none applies. On the bottom you can set the security for this personalization. You can use industry, responsibility or user. I will set this personalization to perform just for one responsibility. Check Fig1.
Now we need to set an action for this personalization, go to the Actions tab. Input a sequence and a description for the action. Set type as Menu. On the right set a Menu entry. Be sure that you don't override one of the existing menus. All the entries that start with menu can be found under the Actions for menu. All the ones that start with Special can be found under the Tools form Menu. For this example we will use the Tools menu. Set up menu entry as Special 15. Set the menu label as 'Call Procedure'.
You can also set the menu to be enabled only on a certain form block. Save your work. Check Fig2.
Now go to the condition tab again. Create a new sequence and a description for the personalization. Set the description as 'Call Procedure'. On trigger event now set the menu we just created (Special 15). Go to the actions tab.
Set a sequence for the action and choose Builtin type. Now on the right under Builtin type set Execute a procedure. In argument we need to input the call to the procedure, this can be a little tricky, so review this example carefully:
='declare v_field_value VARCHAR2(500);begin scheme.procedure('''||${item.tolines_blk.header_id.value}||''',
'''||${item.tolines_blk.line_id.value}||''','''||fnd_global.user_id||''','''||fnd_global.org_id||''');
end'
Basically what we are doing is to call the procedure and passing the necessary arguments. The procedur in the example uses four input parameters. I have put two types of arguments, ones are items within the form (item.tolines_blk.header_id.value) and the other ones are sql statements (fnd_global.user_id). You can also pass constant values to the procedure.
Your procedure structure should be like the following:
Create procedure (argument1 argument type
....)
...Validations
...Save message in a variable
...Perform actions if validations passed
...Save message in custom table
End
In order to show the output message to the user, what we do is to save it into a table and the just query it up. You can use a table that has the following structure
Field Type Description
USER_ID Number Stores the user_id for the message
MESSAGE VARCHAR2(1000) Stores the output message
STATUS Varchar2(1) Status flag for the message
You can use the following sentence to create the table:
create table table_name (user_id number, message varchar2(1000), status varchar2(1));
After you created the table, remember to grant access to the user apps so the table can be accessed from Oracle EBS.
grant all on table_name to apps
On your procedure you should be cleaning the table for the specific user_id just before inserting the new message, this way you will only have one message for every user at any time. You should do something like this at the end of your procedure for every execution:
DELETE FROM table_name where user_id = p_user_id
INSERT INTO table_name VALUES (p_user_id,p_message,p_estado);
Now save your work. Check Fig4
Create a new personalization. Set a sequence and as description set 'OUTPUT MESSAGE'.
Input responsibility security.
You can set up a condition for this personalization, for instance since we are saving a status in the message table so we can send no message if the status is successful and we can send a message if status is unsuccessful.
Now go to the actions tab and set the action type as message.
Select Show as message type and under the message text set the following:
=(select message from my_log_table where user_id = (select fnd_global.user_id from dual))
This will bring up the message stored in the message table.
Save your work.
Now close the form and reopen it. This will make the personalization to take effect.
Go to the Tools menu and launch the procedure. Check Fig 5 and 6.
And that's pretty much of it.
I didn't put a sample procedure since this should be developed based on your needs. Bottom line here is to know that we are able to launch a procedure within Oracle Personalization without the need to modify Oracle Forms.
[Updated on: Thu, 15 March 2012 15:33] Report message to a moderator |
|
|
|
|
|
| Re: Data source for fields in a form [message #547747 is a reply to message #547685] |
Fri, 16 March 2012 04:16 |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I don't know what you've done so far, but - here's "tutorial" for the simplest search you can perform. If you're lucky, you'll find that item. If not, well, follow what people have said.
- Run Forms Builder
- Open a form
- Check the screenshot. On the right, there's a canvas which contains STATUS_NAZIV item which is not a database item (see its Property Palette, below).
- Navigate to Edit - Find and replace PL/SQL menu
- In "Look Where", choose your form (there might be libraries as well. I guess it wouldn't harm if you search them all)
- In "Find What", enter item name (status_naziv in my case)
- Push "Find all" button
- PL/SQL program units that contain that item are displayed in the "Results" window
Good luck!
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
