Skip to main content

Morning all,

I’ve recently had a request to delete subsequent approvals if a specific approval has been rejected. e.g. below: If the PROC REVIEW Request is rejected then the 3 approvals below would be deleted.

I’ve managed to create a custom event as per below, which executes a package to handle the removal:

SQL Package:

   PROCEDURE Remove_Supplier_Approvals(i_Key_Ref VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
   -- p0 -> __lsResult
   p0_ VARCHAR2(32000) := NULL;
   -- p4 -> __sAction
   p4_ VARCHAR2(32000) := 'DO';
   /*Created by RK to automatically remove values from the approvals screen*/
   cursor GET_DATA IS
Select distinct
OBJID,
OBJVERSION from approval_routing
where LU_NAME = 'SupplierInfoGeneral'
and KEY_REF = i_Key_Ref
and description in ('Supp Reactivation - Acc Payable Review',
'Supp Reactivation - Procurement Confirm',
'Supp Reactivation - Reactivate Account')
and APPROVAL_STATUS is null
;
BEGIN
    --IFSAPP.Language_SYS.Set_Language('gb'); -- This requires logged in user to have privileges to Language_SYS.Set_Language
   for C_GET_DATA in GET_DATA LOOP
   
approval_routing_api.Remove__( p0_ , C_GET_DATA.objid , C_GET_DATA.OBJVERSION , p4_ );
end loop;
COMMIT; 

However, when this executes I get the deadlock detected error as it’s attempting to update the same package twice.

 

Any ideas on how to get around this or if there’s some functionality already in IFS that can complete this task?

Kind Regards,

RK

Hi,

You might want to look up a recent discussion here about triggers and mutating tables. There are a few approaches there that one can try and one of them might solve your problem (look for the solutions that uses background jobs to execute the code you want.)

Good luck!

/Mathias

PS. Why do they want to remove the later steps? As soon as a step is rejected, the process should stop. Or at least that's how it is for document approvals.

 

Morning Mathias,

 

I followed the excellent example on: 

https://dsj23.me/2021/08/27/tips-to-avoid-mutating-table-error-in-ifs-event-actions/

Which I think was wrote by someone in the community.

To resolve this problem I did as follows:

  • Created a package to handle the removal, utilising the IFS remove API (package in my question above)
  • Created a custom event that would trigger once a rejection was complete on a specific approval description. (Event in my question above)
  • Created a custom event action that implements a defered background job which calls my package to handle the removal. (Statement below)

DECLARE 
    attr_         VARCHAR2(32000);
    sql_msg_      VARCHAR2(32000);
    stmt_         VARCHAR2(32000);
    job_id_       NUMBER;
    error_text_   VARCHAR2(2000);       
BEGIN
stmt_ := '
DECLARE                                  
BEGIN
 rk_contract_mgmt_api.Remove_Supplier_Approvals(''&NEW:KEY_REF'');
END;';
sql_msg_ := Message_SYS.Construct('UPD');
   Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);
   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
   Client_SYS.Add_To_Attr('MSG_', '', attr_);
   Transaction_SYS.Deferred_Call(job_id_,'Fnd_Event_Action_API.Action_Executeonlinesql','PARAMETER',attr_,'Update Supplier approvals');    
END; 

Regards,

Ryan

Great!

All credits go to @dsj 

 

Reply


Terms & ConditionsCookie settings