3 Replies Latest reply: May 30, 2013 9:55 AM by CraigB RSS

    How can implement running total in oracle forms

    1010834
      I want to implement a running total in oracle form
      like
      100 100
      300 400
      200 600
      500 1100
      200 1300
      100 1400
      PROCEDURE calculate_srno IS
      current_rownum     integer:=:System.Cursor_Record;
      starting_srno integer:=:rs_1;
      last_rownum integer;
      BEGIN
           last_record;
           last_rownum:=:system.cursor_record;
           go_record(1);
           FOR counter IN 1..last_rownum LOOP
                :sum_1:=:rs_1;
                :rs_1:=:sum_1+:rs_1;
           --     :offered_srno_to:=starting_srno;
                if last_rownum=:System.Cursor_Record then exit;
                end if;
           END LOOP;
      END;

      it is not working after want to insert between in the table
        • 1. Re: How can implement running total in oracle forms
          InoL
          it is not working after want to insert between in the table
          That is very cryptic. What do you mean?
          Furthermore, this is just a procedure. How are you using it? In which trigger is this procedure called?

          It can be as simple as this example of a KEY-EXEQRY trigger (where :emp.run_total is a non-database item holding the running total):
          declare
            l_run_total   number := 0;
          begin
            execute_query;
            go_record(1);
            loop
                   l_run_total := l_run_total + nvl(:emp.sal,0);
                   :emp.run_total := l_run_total;
                   exit when :system.last_record = 'TRUE';
                   next_record;
            end loop;      
            go_record(1);
          end;  
          Edited by: InoL on May 24, 2013 10:13 AM
          • 2. Re: How can implement running total in oracle forms
            Bhushan53
            The suggestion which InoL gave is the perfect one. The procedure is also neat.
            But, it is useful only when you have the first column as database column and the data is saved in a database table. The totals will be seen when you query the records and it won't be dynamic.
            If you want it dynamic, you can use a timer as well. Call the same procedure on when-timer-expired. So that, if user changes the first value (:emp.sal for example) in any of the records, the summary (:emp.sal_total) will be updated after a particular time interval.
            • 3. Re: How can implement running total in oracle forms
              CraigB
              Why write code for this? Oracle Forms 10g has Calculated/Summary fields that will do this for you without the need of writing any PL/SQL.

              Simply add a non-table item to the data block with the item you want to keep the running total for. Then change the following properties of the item:
              Data Type: Number
              Calculation Mode: Summary
              Summary Function: Sum
              Summarized Block: <Your Data Block>
              Summarized Item: <Your Block Item>
              Number of Items Displayed: 1 (if your item is in a Multi-Record (Tabular) layout else you don't need to change this property.

              You will also need to change the following property of the block: Query All Records: Yes

              If changing the Block's "Query All Records" property causes your form to be too slow because of the number of records in your block, then you could keep a running total manually by using a combination of the Post-Query and When-Validate-Item (WVI) triggers. If your block does not allow data entry, then you could do this with just the Post-Query trigger. For example:
              BEGIN
                /* This code sample assumes you have added a non-table item */
                /* to your block called SUMMARY.  */
                :YOUR_BLOCK.SUMMARY := :YOUR_BLOCK.SUMMARY + NVL(:RS_1,0);
              END;
              If your block allows data entry, then you would add the following to your existing WVI trigger.
              BEGIN
                /* Perform your data entry validation logic first... */
                IF ....
                
                ELSE
                  ...Validation is successful...
                  :YOUR_BLOCK.SUMMARY := :YOUR_BLOCK.SUMMARY + NVL(:RS_1,0);
                END IF;
              END;
              Hope this helps.

              Craig...