Solved

Mutating trigger on VOUCHER_TAB

  • 21 March 2023
  • 4 replies
  • 97 views

Userlevel 3
Badge +10

Hello,

 

I have the following code that works with no issues in Apps8, however in Apps 10 gives a mutating trigger on VOUCHER_TAB.  How can I overcome this please ?

 

Declare 

account_ number;
count_  number;

BEGIN

select count(*),LISTAGG(account, '; ')WITHIN GROUP (ORDER BY account) into count_, account_
FROM ifsapp.voucher_row
 where company = '&NEW:COMPANY'
   and accounting_year = '&NEW:ACCOUNTING_YEAR'
   and voucher_type = '&NEW:VOUCHER_TYPE'
   and voucher_no = '&NEW:VOUCHER_NO'
   and (substr(account, 0, 4) = '9000' or account = '13009000')
and '&NEW:APPROVED_BY_USERID' not in 
     (select pia.user_id from IFSAPP.company_pers_assign cpa
left outer join ifsapp.PERSON_INFO_ALL pia 
on cpa.emp_no = pia.person_id
where cpa.valid_to >= sysdate
and cpa.pos_code in 
          (select cpp.pos_code from COMPANY_POS_PROPERTY_ALL cpp where cpp.property_code = 'VOUCH_APP' and cpp.PROPERTY_VALUE = 'TRUE'))
and '&NEW:APPROVED_BY_USERID' <> 'IFSAPP';

if 
 count_ > 0 
then
 error_sys.Record_General('Voucher','NOT AUTHORISED, status "Approved" is not permitted on vouchers using accounts like ' || account_);
else null;
end if;

END;

icon

Best answer by Tomas Ruderfelt 22 March 2023, 07:51

View original

4 replies

Userlevel 3
Badge +10

Hello,

 

I have the following code that works with no issues in Apps8, however in Apps 10 gives a mutating trigger on VOUCHER_TAB.  How can I overcome this please ?

 

Declare 

account_ number;
count_  number;

BEGIN

select count(*),LISTAGG(account, '; ')WITHIN GROUP (ORDER BY account) into count_, account_
FROM ifsapp.voucher_row
 where company = '&NEW:COMPANY'
   and accounting_year = '&NEW:ACCOUNTING_YEAR'
   and voucher_type = '&NEW:VOUCHER_TYPE'
   and voucher_no = '&NEW:VOUCHER_NO'
   and (substr(account, 0, 4) = '9000' or account = '13009000')
and '&NEW:APPROVED_BY_USERID' not in 
     (select pia.user_id from IFSAPP.company_pers_assign cpa
left outer join ifsapp.PERSON_INFO_ALL pia 
on cpa.emp_no = pia.person_id
where cpa.valid_to >= sysdate
and cpa.pos_code in 
          (select cpp.pos_code from COMPANY_POS_PROPERTY_ALL cpp where cpp.property_code = 'VOUCH_APP' and cpp.PROPERTY_VALUE = 'TRUE'))
and '&NEW:APPROVED_BY_USERID' <> 'IFSAPP';

if 
 count_ > 0 
then
 error_sys.Record_General('Voucher','NOT AUTHORISED, status "Approved" is not permitted on vouchers using accounts like ' || account_);
else null;
end if;

END;

 

I have done some more digging and the view Voucher_Row used the table VOUCHER_TAB in Apps 10 but not Apps 8.  How can I get the information that I need from voucher_row ??

Userlevel 7
Badge +21

Hi @JannetteC ,

 

You'll need to either change your event trigger to use a different table or defer you procedure to a background job such as in this post.  

 

 

Regards,

William Klotz

Userlevel 7
Badge +19

You can use voucher_row_tab instead of voucher row view, since you are in an event you can use tables also.

Userlevel 7
Badge +22

Hi @JannetteC 

did you check this?

Error while creating PR | IFS_PURCHASE_REQ_LINE_TAB is mutating | IFS Community

 

Reply