Skip to main content

I have the following code, that updates the required date on Project Navigator Demand based on a custom field. It runs fine as a custom menu, but when i run it as an event on creation of a new demand line, it crashes with a mutating trigger error. I believe it’s because I’m selecting info from the table I’m trying to update. Whats the best way to get this to work? TIA.

 

PROCEDURE Update_ProjMiscDem_ReqdDate(MATR_SEQ_NO_ IN VARCHAR2) IS
attr_              VARCHAR2(1000);
info_              VARCHAR2(1000);
offset_            NUMBER;
objid_             VARCHAR2(1000);
objversion_        VARCHAR2(1000);

CURSOR get_parts IS
SELECT p.objid, p.objversion, p.activity_seq
FROM project_misc_procurement p
WHERE p.matr_seq_no = matr_seq_no_;

BEGIN
info_       := NULL;
OPEN get_parts;
FETCH get_parts INTO objid_, objversion_;
CLOSE get_parts;

offset_     := work_time_calendar_api.Get_Work_Days_Between('*',
                                activity_cfp.Get_Cf$_Receive_Parts_By(activity_cfp.Get_Objkey(activity_seq_)), 
                                activity_api.Get_Early_Finish(activity_seq_));


CLIENT_SYS.CLEAR_ATTR(attr_);
CLIENT_SYS.ADD_TO_ATTR('OFFSET', offset_, attr_);
 

PROJECT_MISC_PROCUREMENT_API.Modify__(info_, objid_, objversion_, attr_, 'DO');
COMMIT;
END:

Hi,

Yes this is a common issue where you could end up with a mutating trigger if trying to update table for which you are selecting from, so the only real way around this is to submit the processing to the background queue. 

You can submit the modify to the background queue by using below:

    Ifsapp.Transaction_Sys.Deferred_Call(Id_, 'PROJECT_MISC_PROCUREMENT_API.Modify__', 'ATTRIBUTE', attr_,
        '**DESCRIPTION FOR BACKGROUND JOB**', sysdate, 'FALSE', Queue_Id_);

You need to specify a queue ID in the above call either through a variable or hardcoding it into the call. 

Regards

Shaun

@SHAUN_KERSLAKE Thanks. I’d seen something similar on this forum. 

Currently though attr_ just contains the field OFFSET, do I need to amend it to include OBJVERSION & OBJID?

 

How does it know which record to update otherwise?

Transaction_Sys.Deferred_Call does not work with procedures with OUT parameters. You will have to make procedure on the database that only takes IN parameters and then call  PROJECT_MISC_PROCUREMENT_API.Modify__ from there.

Something like this:

call:

Transaction_Sys.Deferred_Call('CUSTOMER_API.CreateJob', 'ATTRIBUTE', attr_, 'job desc');

procedure:

procedure CreateJob(attr_ in varchar2)
 is
 begin

 PROJECT_MISC_PROCUREMENT_API.Modify__(info_, objid_, objversion_, attr_, 'DO');

end CreateJob;

 

Reply


Terms & ConditionsCookie settings