asked on

summary items in oracle forms

i have 3 tables
v_stat_m

ENTRY_ID                       NOT NULL NUMBER(6)                                                                                                                                                                                     
ENTRY_NO                                NUMBER(10)                                                                                                                                                                                    
ENTRY_DATE                              DATE                                                                                                                                                                                          
BENIFICIARY                             VARCHAR2(100)

Open in new window

v_emp

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

v_stat_d

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
form.bmp

Mark Geerlings

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

NiceMan331

ASKER

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

ASKER CERTIFIED SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NiceMan331

ASKER

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 ?

Mark Geerlings

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.

NiceMan331

ASKER

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 ?

Mark Geerlings

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;
--
BEGIN

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');
ELSE
      MESSAGE(errm);
END IF;

      IF (errt IN ('F','E','W','I')) THEN
            if Id_Null(alert_id) THEN
                  message(errm);
            else
                  SET_ALERT_PROPERTY(alert_id,ALERT_MESSAGE_TEXT,errm);
                        alert_button := SHOW_ALERT(alert_id);
            end if;
      END IF;

      IF (rftf) THEN
            RAISE FORM_TRIGGER_FAILURE;
      END IF;

END;

NiceMan331

ASKER

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

Mark Geerlings

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.

NiceMan331

ASKER

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

Mark Geerlings

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

NiceMan331

ASKER

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