summary items in oracle forms

Posted on 2014-03-04
Medium Priority
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
  • 6
  • 6
LVL 36

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 36

Accepted Solution

Mark Geerlings earned 2000 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.
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.


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 36

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 36

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 36

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 36

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

607 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