i make form , header , for v_stat_m
then detail v_emp
then 2nd detail v-stat_d
i make summary column on block v-stat_d for 2 columns , debit and credit
now i need same summary items for those 2 column but on header level
i mean to caluculate summary for all debit & credit for whole records under one v_stat_mform.bmp

The suggestion I gave you will only populate those summary fields when that block is queried (and detail records in v_stat_d already exist). That suggestion will not populate those summary fields automatically

Create a view to do those calculations, and include in the view the other columns from the v_stat_m table that you need in this form (plus include a column for the rowid of the v_stat_m table). Then change the "Query_Data_Source" for your v_stat_m block to use this view instead of the base table. Leave the DML_Data_SOurce to use the table directly. Then add these columns to that block as database fields, and change them to "Query_Only=Y". You may (or may not) need to create an "ON-LOCK" trigger to replace the built-in record-locking mechanism. (I can post a sample ON-LOCK trigger here if you need that.)

The suggestion I gave you will only populate those summary fields when that block is queried (and detail records in v_stat_d already exist). That suggestion will not populate those summary fields automatically when new records are added to the v_stat_d table.

If you want those fields to be populated in both situations (as new v_stat_d records are being added or when existing records are being queried) you will have to do some things differently.
1. Change the summary fields that I suggested yesterday (that are based on a view) to be non-displayed in the v_stat_m block.
2. Create two new displayed, but non-base-table, fields to display these values.
3. Add a post-query trigger in the v_stats_m block to copy the values from the (now, non-displayed) base table summary items to the new displayed items.
4. Add a post-insert trigger in the v_stat_d block to add the new values from the v_stat_d record to the displayed, summary values in the v_stat_m block.
5. If you allow these v_stat_d values to be modified, you will also need a post-update trigger on the v_stat_d block to adjust the displayed values in the v_stats_m block to reflect the new values. You will need to add non-displayed, non-base-table items in the v_stats_d block to hold the "before" values of the debit and credit fields, so the post-update trigger can apply just the difference to the summary values in the v_stat_m block. You will need to copy the values from the displayed, base-table debit and credit fields in this block with: post-query, post-insert, and post-update triggers to the non-displayed "before" fields for debit and credit so the post-update trigger will always have correct values to work with.
6. If you allow v_stat_d records to be deleted, you will also need a pre-delete trigger in this block to subtract the debit and credit values from the displayed values in the v_stat_m block.

Here is an ON-LOCK query that I wrote for one of our form blocks (named: edi_cumm_summary) that was based on a view (that included the rowid of the base table, which is: item_xref). In the exception handler, this trigger also calls a custom procedure (MSG_ALERT) that we have in a *.PLL file that is attached to all of our forms. This procedure simply displays an alert with the text that we pass to it. You could use a simple MESSAGE call instead if you prefer, but then you need to add an explicit RAISE_FORM_TRIGGER_FAILURE.

declare
cursor c1 is select 1
from item_xref
where rowid = :edi_cumm_summary.xref_row
for update of cumm nowait;
dummy number;
begin
if :edi_cumm_summary.xref_row is null then
null;
else
--We need to reserve the item_xref record
open c1;
fetch c1 into dummy;
close c1;
end if;
exception
when others then
if c1%isopen then
close c1;
end if;
msg_alert('Someone has the cross-reference record locked, requery and try this change later.','E',TRUE);
end;

"what is the benifit of this trigger?"
Normally, Oracle Forms will include logic in the *.fmx file to do this for you automatically, and you will not see the SQL in the *.fmb file. But, when you base the Query_Data_Source on a view instaed of on a table, the default record-locking logic of oracle Forms will not work for you. So, you have to write a ON-LOCK trigger to do this action for you in a block like this.

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

The ON-LOCK trigger must be at the block level, on the block that has its Query_Data_Source set to a view instead of a base table.

Here is the source code for our MSG_ALERT trigger. You could either include this in your form, or (a better option in my opinion) you could put this in a *.PLL file and attach this to all of your forms.
(Note that it uses three named alerts: STOP, CAUTION or NOTE that you will have to create in each form.)

In Forms Builder, choose: File, New, PL\SQL library. Give it a name you like (we used "common.pll" ). Then create a "Program unit" (procedure) with this name, compile it, and save the *.pll file, and generate the *.plx file (under; File, Administration, Compile File).

Then open your *.fmb file, and in the navigator pane, highlight the "Attached Libraries" node, then click: Navigator, Create and give it the name of the *pll file you created. Choose "File system" then "attach" and say yes, remove the non-portable path.

But this assumes you will copy all of your *.fmx and *.plx files to a common location and run them from there. I recommend that that location be a new directory you create for this purpose, and not the default location which is buried a few levels deep in your Oracle_home. You then should create an icon to launch your startup form, and set the "Start in" (or "Working directory") for this icon to this directory path.

i know your solution is great , but i still not understood the on_lock trigger
so , i select the easiest way for me
sorry , i adjust like this :
1- i bring back data_source for block v-stat_m to table
2-i removed the desplayed items of summary (view)
3-i added 2 desplayed items , non database ,
4-i added save button to commit each set of records in v_stat_d (v_emp) block level
5- i added this code when button pressed for save button

select sum(debit) into :V_STAT_M.T_DEBIT from v_stat_m1 where entry_id = :v_stat_m.entry_id; select sum(credit) into :V_STAT_M.T_credit from v_stat_m1 where entry_id = :v_stat_m.entry_id;

same code on , pre_query for v_stat_m
now every thing is ok , any insert , delete , or edit in v_stat_d , the grand total is updated
but only when button pressed
now i'm worry for one thing , if user used exit form for example , oracle will ask to save changes , if select yes , the total will not updated
i didn't found any : om_commit trigger , how i can let my save button exclusive for commit and avoid closing or saving any other way

You don't need the grand total to be updated, because you aren't storing that anywhere, correct? You just have view calculate it at query time and the form calculate it when records are added, changed or deleted.

If you do want to store this total on the master record, that violates the rules for data normalization and that complicates your life as a programmer, and that makes another place for things to go wrong, if the stored totals ever get out-of-sync (like if someone ever does an insert, update or delete of the detail records via SQL*Plus, SQL Developer, TOAD, or any other tool outside of your Oracle Forms).

In some applications, it may make sense to store these totals on the master record, for performance reasons at query time. But, in most transaction-processing systems, your life will be a lot simpler if you don't attempt to store these calculated summary values, and just let the view calculate them when users want to display them.

You didn't find an "ON-COMMIT" trigger, because by default Oracle Forms does this action for you without a trigger. But, you may write an ON-COMMIT trigger if you want to. I don't recommend doing that though unless or until you have a very good understanding of what Oracle Forms does for you by default, and you are sure that a custom ON-COMMIT trigger would be the best way to solve a problem. I've developed a few hundred Oracle forms, and I've only ever used an ON-COMMIT trigger in a few of them (probably in less than 1% of the forms I've worked on).

i really intersted in all your posts here , i admit that it added alot to my knowledge
for my side , i not preffere to store any calculation , as you menstiobed let it simply
but let me explain the purpose of the form , you may teach me more
this form will record information of one time payments to many employee , each employee been paid a couple of benifits ,
for example :
today 6-mar-14 , insert new records on v_stat_m to prepare a check to pay some employee

employee name : A ( insert new record on v_emp for empl info )
then insert some records on v_stat_d to calculate details of his benifts
salary , 1000
over time 500
housing 300
transportation 200
total benifit of employee A = 2000 $ (summary item on block v_stat_d)

then new record on v_emp for employee B
total is 3000 $
----
-----
here we need the grand total at the end to update total to pay all , where they will issue one check for total all
hope i explained well

0

Featured Post

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Subquery in Oracle:
Sub queries are one of advance queries in oracle.
Types of advance queries:
• Sub Queries
• Hierarchical Queries
• Set Operators
Sub queries are know as the query called from another query or another subquery. It can …

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here.
General principle of Oracle compression
Oracle compression is a way of reducing the d…