| How to re-query a master-detail-subdetail form after writing changes to the database? [message #146337] |
Wed, 09 November 2005 23:15  |
adamjsawyer
Messages: 79
Registered: April 2005
Location: Perth, Western Australia ...
|
Member |
|
|
Hi,
In my form, to perform calculations, I currently manually navigate to each field on my master-detail-subdetail form, calculate the values, write them into the form fields and save them back to the database. The calculations are taking some time and I think this might be due to my manual navigation around the form.
My question is, can I calculate and save directly to the database, and then requery the new values so they appear back on the form? There would only be changes to one master invoice and its detail records, but any other invoices that were included in the previous query would still need to be available (ie: they must still be able to navigate to the other unchanged invoices via the smartbar)
I am using Forms 9i, and am saving the calculations into columns in the database, rather than using formulae/summaries. This is because the calculations are very complex and use about 800 lines of PLSQL code.
If I navigate to the master block and perform an EXECUTE_QUERY after writing the calculations to the database, will this work? Will it retrieve all the records in the last EXECUTE_QUERY, or just the one that is currently displayed?
Hope this makes sense and someone can help
cheers
adam
|
|
|
|
|
|
|
|
| Re: How to re-query a master-detail-subdetail form after writing changes to the database? [message #146558 is a reply to message #146524] |
Fri, 11 November 2005 01:18  |
adamjsawyer
Messages: 79
Registered: April 2005
Location: Perth, Western Australia ...
|
Member |
|
|
I created the form using oracle designer, and i am not allowed to change the database or regenerate from designer again. I have to make do with what I have now. Surely, I can just call procedures from package created in TOAD to calculate the values into the database, and then run an execute query in the middle detail to pull the new data out into the middle detail and sub-detail on the form? I'm really pushed for time because this section of the project needs to be completed within a few days, so the easiest method is going to have to be it. I'm liking the idea of the putting the calculations into a package, because the next step in the project is converting the forms into web plsql using OWA tools over the application server. Currently the code in the form would not be reusable for this.
thx
adam
|
|
|
|
| Re: How to re-query a master-detail-subdetail form after writing changes to the database? [message #146749 is a reply to message #146558] |
Sun, 13 November 2005 16:51 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Okay, no sweat. Use Post-Query trigger to populate all the other fields of your record. This does the work one record at a time. You COULD put the formulas into this trigger and recalculate everything, boring. You could have one or more 'selects'/'cursors' to get the data, so so. Write a procedure (in or out of a package) to which you pass the key fields and which, in turn, does the retrievals and any necessary calculations, and returns the required display items.
Post-Query triggerdeclare
l_dummy1 mytable.myitem%TYPE;
begin
mypkg.myproc (:myblk.key_item1,
:myblk.key_item2,
:myblk.key_item3,
:myblk.display1,
:myblk.display2,
:myblk.display3,
:myblk.display4,
:l_dummy1);
end;
This procedure uses three key fields, which exist in the block, and returns five (5) values, four (4) of which I want to display in this form and a fifth that I want to use somewhere else (another form which may/may not use one of the other returned fields, for example).
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
