fetching sum of Dr and Cr customer wise accounting

Question

Oracle Form : 12

Weblogic :12.0.01

Oracle DB : 19 c

The code is under the trigger of ‘When new form instance’ oracle form

declare
CURSOR c1 IS

SELECT id, Customer, SUM(DEBIT ) as Debit, abs(SUM(Payment )) as PAYMENT
FROM
(
SELECT
Cust_id as id,
cust_name as customer,
OPENING_BLNC as Debit,
0 as PAYMENT FROM Customer
union all
select
c.Cust_id as id,
c.cust_name as customer,
i.total_amount as Debit,
0 as PAYMENT
FROM Customer c,transaction t,invoice i where t.tran_id=i.inv_tran_id and c.cust_id=t.cust_id
union all
select
c.Cust_id as id,
c.cust_name as customer,
0 as Debit,
a.cr as PAYMENT
FROM Customer c,accounts a where c.cust_id=a.cust_id
)
GROUP BY id, Customer order by id desc ;

begin

FOR lop1 IN c1
loop
:block28.id:=lop1.id;
:block28.customer:= lop1.customer;
:block28.debit:=lop1.debit;
:block28.payment:= lop1.payment;
END LOOP;

end;

The out put of this code is ::

ID Customer Total dues Total Payment

1 KARIMULLAH 68697.04 40000

and result output should be.

ID Customer Total dues Total Payment

3 AZAD MEDIICE 21519.62 5000

2 NEW KARIM AGENCY 0 10000

1 KARIMULLAH 68697.04 40000

Answer 1

That's because you're overwriting the same tabular form row over and over again. You have to move to the next record, i.e.

FOR lop1 IN c1
loop
  :block28.id       := lop1.id;
  :block28.customer := lop1.customer;
  :block28.debit    := lop1.debit;
  :block28.payment  := lop1.payment;

  next_record;              --> this
END LOOP;

---

[EDIT]

You commented that you didn't manage to do it properly and got FRM-40102. It means:

Cause:  You pressed [Next Record] or [Down] in a context where it is meaningless. Either:

1. The last record in a block is the current record.
2. The block is empty.
3. You are in a new record in the middle of the block created by pressing [Insert Record].

Action:  No action is necessary.

I created sample form which works as expected. Here's how:

When ran, form results in this:

As you can see, there's no error, block is populated with data and cursor is now in an empty record #5, waiting for new data to be inserted (or whatever you want to do).

Therefore, if it didn't work for you, you probably did something wrong. Can't tell what, but - is there any other block? Is that block current and NEXT_RECORD doesn't make sense there? If so, GO_BLOCK to block28 first!