Nevermind, I fixed it by using an explicit cursor:
DECLARE
info_ VARCHAR2(2000);
attr_ VARCHAR2(2000);
automatic_pay_auth_flag_db VARCHAR2(1):= '1';
CURSOR get_supp IS
SELECT * FROM IDENTITY_INVOICE_INFO where automatic_pay_auth_flag_db != '1';
rec_ get_supp%ROWTYPE;
BEGIN
OPEN get_supp;
LOOP
FETCH get_supp INTO rec_;
EXIT WHEN get_supp%NOTFOUND;
Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('AUTOMATIC_PAY_AUTH_FLAG_DB',automatic_pay_auth_flag_db,attr_);
IDENTITY_INVOICE_INFO_API.Modify__(info_,rec_.objid,rec_.objversion,attr_,'DO');
END LOOP;
CLOSE get_supp;
COMMIT;
END;
Hi,
problem was, that you used same cursor inside loop over same cursor.
It is unfortunately not allowed, even if you want to open it with different arguments.
In your case it make no sense to do it like that - because that loop body will never be executed when there is no record. So there is no reason to explicitly exit loop with that construction.
FOR rec_ IN get_supp LOOP
Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('AUTOMATIC_PAY_AUTH_FLAG_DB',automatic_pay_auth_flag_db,attr_);
IDENTITY_INVOICE_INFO_API.Modify__(info_,rec_.objid,rec_.objversion,attr_,'DO');
END LOOP;
is equal to
OPEN get_supp;
LOOP
FETCH get_supp INTO rec_;
EXIT WHEN get_supp%NOTFOUND;
Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('AUTOMATIC_PAY_AUTH_FLAG_DB',automatic_pay_auth_flag_db,attr_);
IDENTITY_INVOICE_INFO_API.Modify__(info_,rec_.objid,rec_.objversion,attr_,'DO');
END LOOP;
CLOSE get_supp;
BR
One more thing, IDENTITY_INVOICE_INFO contains data for both customers and suppliers.
I’m not sure about what you are trying to update but if you want to limit it to any of those types you can add a condition to field PARTY_TYPE_DB to be equal to CUSTOMER or SUPPLIER.