summary items in oracle forms

Posted on 2014-03-04
Last Modified: 2014-03-14
i have 3 tables
ENTRY_ID                       NOT NULL NUMBER(6)                                                                                                                                                                                     
ENTRY_NO                                NUMBER(10)                                                                                                                                                                                    
ENTRY_DATE                              DATE                                                                                                                                                                                          
BENIFICIARY                             VARCHAR2(100)        

Open in new window


EMP_ID                         NOT NULL NUMBER(15)     pk                                                                                                                                                                               
MAIN_ID                                 NUMBER(5)                  fk  on v_stat_m.entry_id                                                                                                                                                                   
V_STAT_TYPE                             NUMBER(1)                                                                                                                                                                                     
EMP_NO                                  NUMBER(6) 

Open in new window


SUB_ID                         NOT NULL NUMBER(15)       pk                                                                                                                                                                             
EMP_ID                                  NUMBER(5)                     fk v_emp.emp_id                                                                                                                                                                
EMP_NO                                  NUMBER(6)                                                                                                                                                                                     
RESRV                                   NUMBER(2)                                                                                                                                                                                     
ACC_NO                                  NUMBER(5)                                                                                                                                                                                     
COST_CENTER                             NUMBER(5)                                                                                                                                                                                     
DEBIT                                   NUMBER(10,2)                                                                                                                                                                                  
CREDIT                                  NUMBER(10,2)                                                                                                                                                                                  

Open in new window

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_m
Question by:NiceMan331
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39904795
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.)

Author Comment

ID: 39905675
ok , it done
but look
how to requery those items
because values of those summary still null even after saving the reocrd
LVL 35

Accepted Solution

Mark Geerlings earned 500 total points
ID: 39906250
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.
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 39907401
ok , good idea , i will try to use it tomorrow
by the way
I can post a sample ON-LOCK trigger here if you need that.)
yes plz do ,and what is the benifit of this trigger ?
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39907613
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.

      cursor c1 is select 1
        from item_xref
        where rowid = :edi_cumm_summary.xref_row
          for update of cumm nowait;
      dummy number;
      if :edi_cumm_summary.xref_row is null then
--We need to reserve the item_xref record
    open c1;
    fetch c1 into dummy;
    close c1;
  end if;
      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);

"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.

Author Comment

ID: 39907692
thanx for your typing , but i'm sorry , i t is difficult for my small knowledge
any how , this trigger i will put it in form_level ?
then do i need to create MSG_ALERT procedure ?
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39907739
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.)

procedure msg_alert(
errm in char,           /* message */
errt in char,           /* message type */
rftf in boolean         /* raise form_trigger_failure ? */
) is
alert_id alert;
alert_button number;
IF (errt = 'F') THEN
      alert_id := FIND_ALERT('STOP');
  ELSIF (errt = 'E')THEN
              alert_id := FIND_ALERT('STOP');
  ELSIF (errt = 'W') THEN
      alert_id := FIND_ALERT('CAUTION');
  ELSIF (errt = 'I') THEN
      alert_id := FIND_ALERT('NOTE');
      IF (errt IN ('F','E','W','I')) THEN
            if Id_Null(alert_id) THEN
                        alert_button := SHOW_ALERT(alert_id);
            end if;
      END IF;

      IF (rftf) THEN
      END IF;


Author Comment

ID: 39907761
How to : could put this in a *.PLL file and attach this to all of your forms. ?
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39907830
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.

Author Comment

ID: 39908877
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;

Open in new window

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
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39909280
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).

Author Comment

ID: 39911023
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question