Solved

Inventory Part Revision on Product Structure Revision

  • 14 December 2021
  • 6 replies
  • 444 views

Userlevel 3
Badge +8

Hi,

 

We use the revisions of Inventory Parts to keep the revision our customer is on (Field ENG_REVISION), so when we do an internal revision of our Product Structure we would like to keep the customer revision the same.

For this I started out doing a custom event to copy the ENG_REVISION field from the previous revision, but I am hitting the mutating error.

 

Below is the code I am using for now, and attached is the event ID and all its parameters.
I have read that there are workarounds if trying to use background jobs, but don’t exactly know and understand how to go about doing so.

 

Would appreciate any help!

 

Thanks in advance,
Mendes
 

DECLARE

  new_Eng_Revision VARCHAR2(20) := '';
  new_Eng_Revision_Desc VARCHAR2(20) := '';
  p0_ VARCHAR2(25) := '';
  p1_ VARCHAR2(25) := '';
  p2_ VARCHAR2(25) := '';
  p3_ VARCHAR2(250) := '';
  p4_ VARCHAR2(5) := 'DO';

  CURSOR get_last_revision
  IS
  SELECT OBJID, OBJVERSION, ENG_REVISION, ENG_REVISION_DESC FROM IFSAPP.PART_REVISION
  WHERE CONTRACT='&NEW:CONTRACT' AND PART_NO='&NEW:PART_NO' AND ENG_CHG_LEVEL='&NEW:ENG_CHG_LEVEL'-1;

OPEN get_last_revision;
  FETCH get_last_revision
  INTO p1_,
    p2_,
    new_Eng_Revision,
    new_Eng_Revision_Desc;
CLOSE get_last_revision;

p3_ := 'ENG_REVISION' || chr(31) || new_Eng_Revision || chr(30) ||'ENG_REVISION_DESC'|| chr(31) || new_Eng_Revision_Desc || chr(30);

   --p0 -> __lsResult
   --p1 -> __sObjid
   --p2 -> __lsObjversion
   --p3 -> __lsAttr
   --p4 -> __sAction
   IFSAPP.PART_REVISION_API.MODIFY__( p0_ , p1_ , p2_ , p3_ , p4_ );

END;

icon

Best answer by Hans Andersen 15 December 2021, 07:37

View original

This topic has been closed for comments

6 replies

Userlevel 4
Badge +6

Hi @carlosfmendes ,

I’m assuming by the mutating error you are referring to the ORA-04091 error. In that case, the possible cause would be that you are trying to both select and update the IFSAPP.PART_REVISION table in a row level trigger. 

You could maybe try using a compound DML trigger and see if it works.
https://docs.oracle.com/database/121/LNPLS/triggers.htm#LNPLS99888

Hope this helps!

Regards,
Malindu

Userlevel 3
Badge +8

Hi @carlosfmendes ,

I’m assuming by the mutating error you are referring to the ORA-04091 error. In that case, the possible cause would be that you are trying to both select and update the IFSAPP.PART_REVISION table in a row level trigger. 

You could maybe try using a compound DML trigger and see if it works.
https://docs.oracle.com/database/121/LNPLS/triggers.htm#LNPLS99888

Hope this helps!

Regards,
Malindu

Hi Malindu,

Yes, you are correct, however the error is on top of a Custom Event in IFS, as I am not doing anything directly on the Oracle database.

This is why I ask, if there is a workaround within IFS?

Thanks,
Mendes

Userlevel 4
Badge +6

Hi @carlosfmendes ,

I believe the below post might contain the answer you are looking for:
 


Can you try your custom event again by utilizing PRAGMA AUTONOMOUS_TRANSACTION; ?

Regards,
Malindu

Userlevel 3
Badge +8

Hi @carlosfmendes ,

I believe the below post might contain the answer you are looking for:
 


Can you try your custom event again by utilizing PRAGMA AUTONOMOUS_TRANSACTION; ?

Regards,
Malindu

 

If I understand correctly, this makes an autonomous and asynchronous transaction, correct?
I can see this might work, as the trigger is AFTER creation, and not on any CHANGE or REMOVAL.

So I need the new row created, to then update the fields on this newly created row.

 

However at the moment, I have the problem that I need to get the OBJID and OBJVERSION of the recently created row at the beginning of the custom event, however the SELECT statement returns no OBJID and OBJVERSION, so I assume it is not yet created, even though the custom event is for AFTER creation.


Does this have anything to do with this change, to do put PRAGMA AUTONOMOUS_TRANSACTION?

 

 

Userlevel 6
Badge +14

Hi @carlosfmendes ,

You can make you sql into a string and the execute it as a background job.

See this:

 https://community.ifs.com/framework-experience-infrastructure-cloud-integration-dev-tools-50/how-to-create-a-defered-job-to-execute-modify-1757

 

 

Userlevel 3
Badge +8

Hi @carlosfmendes ,

You can make you sql into a string and the execute it as a background job.

See this:

 https://community.ifs.com/framework-experience-infrastructure-cloud-integration-dev-tools-50/how-to-create-a-defered-job-to-execute-modify-1757

 

 

Hi @Hans Andersen,

Thanks for the tip.
It worked like a charm! :wink: