Hello, I have createed a process to update the table based on diffrence of another 2 table values. Code is working fine but I need to update the record twice. On 1st attempt it does not update the table but in 2nd attempt it is then updaing the table. Please help
begin
declare
v_diff number;
begin
--Getting invoice remaining balance
with id as (select invoice_id, sum(nvl(pcs,0)*nvl(rate,0)) invoice_amount from invoice_detail group by invoice_id)
select nvl(id.invoice_amount,0) - nvl(realization.realized_amount,0)
into v_diff
from id
left join (
select r.invoice_id, sum(nvl(r.fc,0)) realized_amount
from realization r
group by r.invoice_id
) realization on id.invoice_id = realization.invoice_id
where id.invoice_id = :invoice_id;
--Updating Fully_Realized value in invoice_master table
if v_diff = 0 then
update invoice_master set fully_realized = 'Y' where invoice_id = :invoice_id;
else
update invoice_master set fully_realized = 'N' where invoice_id = :invoice_id;
end if;
end;
end;