Solved

UPDATE DATEFIELD EVENT SERVER

  • 23 September 2021
  • 16 replies
  • 125 views

Userlevel 3
Badge +7

Hi,

I’m trying to update a date the latest_start field based on the change of planned_finish by an event server but with no success. 

Inside the application. Using PL/SQL it works well runing the code manually

Could someone help please?

Follow the code:

 

DECLARE
  p1_ DATE;
BEGIN

  SELECT PLANNED_FINISH 
    INTO p1_
    FROM jt_task_uiv
   WHERE TASK_SEQ = &OLD:TASK_SEQ;

  UPDATE jt_task_tab
     SET LATEST_START = p1_
   WHERE TASK_SEQ = &OLD:TASK_SEQ;
commit;
end; 

 

 

 

icon

Best answer by lopespetro 28 September 2021, 15:17

View original

16 replies

Userlevel 7
Badge +13

You are experiencing the classic "mutating table" problem that most people working with triggers (used by a custom event) do. If you search for that here or on the internet you can find various workarounds, as well as an explanation of what it is about. Here is one forum post that mentions it:

https://community.ifs.com/framework-experience-infrastructure-cloud-integration-dev-tools-50/cannot-update-custom-field-from-custom-event-11581
 

Userlevel 3
Badge +7

You are experiencing the classic "mutating table" problem that most people working with triggers (used by a custom event) do. If you search for that here or on the internet you can find various workarounds, as well as an explanation of what it is about. Here is one forum post that mentions it:

https://community.ifs.com/framework-experience-infrastructure-cloud-integration-dev-tools-50/cannot-update-custom-field-from-custom-event-11581
 

Hi, tks for the info.

putting the pragma but getting the deadblock.

 

DECLARE
pragma autonomous_transaction;
  p1_ DATE;
BEGIN

  SELECT PLANNED_FINISH 
    INTO p1_
    FROM jt_task_uiv
   WHERE TASK_SEQ = &OLD:TASK_SEQ;

  UPDATE jt_task_tab
     SET LATEST_START = p1_
   WHERE TASK_SEQ = &OLD:TASK_SEQ;
commit;
end;

 

 

Userlevel 7
Badge +13

You might also want to have a look at this post by @dsj:

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

 

Userlevel 3
Badge +7

You might also want to have a look at this post by @dsj:

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

 

Nice content, but still not working.

Userlevel 3
Badge +7

I’m trying with a custom field but getting the same problem.

Any ideas?

 

DECLARE
p1_ DATE;
BEGIN

SELECT PLANNED_FINISH
    INTO p1_
    FROM jt_task_uiv
   WHERE objkey = '&OLD:ROWKEY';

  UPDATE jt_task_cft
     SET CF$_PRI_PLAN_DATE = p1_
   WHERE rowkey =  '&OLD:ROWKEY';

end; 

 

Userlevel 5
Badge +12

The only way to do this is to create a background job which does the update after everything is saved in your session.

Look at section 2 “Use Background Processing” in the link Mathias posted. There is a piece of code to start with.

Userlevel 7
Badge +13

The SELECT above could probably be removed. You can get the value using the &OLD/NEW syntax instead. Then I assume the UPDATE will go well since that is the custom field table, which is not what you are triggering on.

What do you think, @Tomas Ruderfelt?

 

Userlevel 7
Badge +15

The SELECT above could probably be removed. You can get the value using the &OLD/NEW syntax instead. Then I assume the UPDATE will go well since that is the custom field table, which is not what you are triggering on.

What do you think, @Tomas Ruderfelt?

 

Yes, something like this:

BEGIN
UPDATE jt_task_cft
SET cf$_pri_plan_date = '&OLD:PLANNED_FINISH'
WHERE rowkey = '&OLD:ROWKEY';
END;

 

Userlevel 3
Badge +7

The SELECT above could probably be removed. You can get the value using the &OLD/NEW syntax instead. Then I assume the UPDATE will go well since that is the custom field table, which is not what you are triggering on.

What do you think, @Tomas Ruderfelt?

 

Yes, something like this:

BEGIN
UPDATE jt_task_cft
SET cf$_pri_plan_date = '&OLD:PLANNED_FINISH'
WHERE rowkey = '&OLD:ROWKEY';
END;

 

Hi, good info, but this works only if I already have a line in cft table. Otherwise, there is no rowkey to link to task table.

I tried creating a text custom field with a sysdate default value.

As the user create a task, the system creates a row in cft table with the sysdate. After release the task, the event works. It updates the field with the planned finish.

But there is a situation that I need to block the update of this field after the first time release.

Could you guys have any idea how may I do that?

If the user changes the planned finish, the event is changing the custom field too.

But i need in this custom field just the first planned finish.

The event is created on object changed (rowstate).

 

 

tks for help.

Userlevel 7
Badge +13

> this works only if I already have a line in cft table. Otherwise,
> there is no rowkey to link to task table.

If there is no record in the CFT, you don't need to update it either, right?

> But there is a situation that I need to block the update of this
> field after the first time release.  Could you guys have any idea
> how may I do that?  If the user changes the planned finish, the
> event is changing the custom field too.  But i need in this custom
> field just the first planned finish.

Do you want to add some logic to the code such that the UPDATE only
happens once? If yes, then it might be possible as long as you don't
need to SELECT against the same table to get the information necessary
to know if the UPDATE should happen or not.

Userlevel 3
Badge +7

If there is no record in the CFT, you don't need to update it either, right?
   in fact, I need to insert the planned finish after realease.

I could block the change with another event not letting the user return the status to prepared and after workstarted not return to release.
But I’ve another situation that if the user creates a task from mobile, there is not cft line, just if I create the task in IEE.

Tried with history log but got the same situation “mutating”

If you have an idea, please tell me.

I saw this article but how could I create a database task and call the procedure to insert planned finish date to the lastest_start field after the user realease the wo task?

 


 

 

Userlevel 4
Badge +7

Try to update this field from a background job and instead of using direct update query in the custom event, I recommend you to use Modify/Modify__ method of JT_TASK_API.
you can create background job using another custom event, by following below example:

Event 1:
DECLARE
/*define variables*/
lu_name_ VARCHAR2(8000):= 'EquipmentFunctional';
event_id_ VARCHAR2(8000):= 'C_UPSERT_PM_GROUP_ID_EQBJ';-- call this event
event_msg_ VARCHAR2(8000);
attr_ VARCHAR2(32767);

BEGIN
event_msg_ := Message_SYS.Construct('C_AUTOMATE_DEPENDENCIES'); -- from this event
Message_SYS.Add_Attribute(event_msg_, 'OLD_MCH_CODE', '&OLD:MCH_CODE');
Message_SYS.Add_Attribute(event_msg_, 'OLD_CONTRACT', '&OLD:CONTRACT');

Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('EVENT_DATA_', event_msg_, attr_);
Client_SYS.Add_To_Attr('EVENT_ID_', event_id_, attr_);
Client_SYS.Add_To_Attr('EVENT_LU_NAME_',lu_name_, attr_);

attr_ := REPLACE(attr_, CHR(31) || CHR(31), CHR(31));

Transaction_SYS.Deferred_Call('EVENT_SYS.EVENT_EXECUTE', 'PARAMETER', attr_, 'BJ description'); -- create the background job
END;

Event 2:
DECLARE
/* declare variables*/
CURSOR get_pm_action_info(mch_code_ IN VARCHAR2,
contract_ IN VARCHAR2,
group_log_db_ IN VARCHAR2) IS
SELECT objid, objversion
FROM ifsapp.pm_action_tab pm
WHERE pm.mch_code = mch_code_
AND pm.mch_code_contract = contract_;
attr_ VARCHAR2(32000);
info_ VARCHAR2(32000);

BEGIN
group_log_db_ := 'NETS';

FOR rec_ IN get_pm_action_info('&OLD_MCH_CODE', '&OLD_CONTRACT', group_log_db_) LOOP
Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('ROUNDDEF_ID', rounddef_id_, attr_);
Pm_Action_API.Modify__(info_, rec_.objid, rec_.objversion, attr_, 'DO');
END LOOP;
END;


Hope this helps!

Userlevel 3
Badge +7

Try to update this field from a background job and instead of using direct update query in the custom event, I recommend you to use Modify/Modify__ method of JT_TASK_API.
you can create background job using another custom event, by following below example:

Event 1:
DECLARE
/*define variables*/
lu_name_ VARCHAR2(8000):= 'EquipmentFunctional';
event_id_ VARCHAR2(8000):= 'C_UPSERT_PM_GROUP_ID_EQBJ';-- call this event
event_msg_ VARCHAR2(8000);
attr_ VARCHAR2(32767);

BEGIN
event_msg_ := Message_SYS.Construct('C_AUTOMATE_DEPENDENCIES'); -- from this event
Message_SYS.Add_Attribute(event_msg_, 'OLD_MCH_CODE', '&OLD:MCH_CODE');
Message_SYS.Add_Attribute(event_msg_, 'OLD_CONTRACT', '&OLD:CONTRACT');

Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('EVENT_DATA_', event_msg_, attr_);
Client_SYS.Add_To_Attr('EVENT_ID_', event_id_, attr_);
Client_SYS.Add_To_Attr('EVENT_LU_NAME_',lu_name_, attr_);

attr_ := REPLACE(attr_, CHR(31) || CHR(31), CHR(31));

Transaction_SYS.Deferred_Call('EVENT_SYS.EVENT_EXECUTE', 'PARAMETER', attr_, 'BJ description'); -- create the background job
END;

Event 2:
DECLARE
/* declare variables*/
CURSOR get_pm_action_info(mch_code_ IN VARCHAR2,
contract_ IN VARCHAR2,
group_log_db_ IN VARCHAR2) IS
SELECT objid, objversion
FROM ifsapp.pm_action_tab pm
WHERE pm.mch_code = mch_code_
AND pm.mch_code_contract = contract_;
attr_ VARCHAR2(32000);
info_ VARCHAR2(32000);

BEGIN
group_log_db_ := 'NETS';

FOR rec_ IN get_pm_action_info('&OLD_MCH_CODE', '&OLD_CONTRACT', group_log_db_) LOOP
Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('ROUNDDEF_ID', rounddef_id_, attr_);
Pm_Action_API.Modify__(info_, rec_.objid, rec_.objversion, attr_, 'DO');
END LOOP;
END;


Hope this helps!

Hi Mit, tks for help and info.


I tried method Modify/Modify__ but it says is a function or has parameters that are not supported.


But The method Modify Earliest Start is OK. Could you help to set the parameters expression?

Tks a lot!

 


 

Userlevel 4
Badge +7

@lopespetro I’m not sure how to setup database task values according to your case.
But I have attached an example for your reference on parameter default values.

 

I think it would be better, if you go with Modify__ and its similar to this post community link .
That is trying to update a custom field but you can use the same logic for your purpose. 
Most of the data you need, can get directly from custom event attributes, same as ‘&OLD:TASK_SEQ’ which you have already used.

Userlevel 3
Badge +7

@lopespetro I’m not sure how to setup database task values according to your case.
But I have attached an example for your reference on parameter default values.

 

I think it would be better, if you go with Modify__ and its similar to this post community link .
That is trying to update a custom field but you can use the same logic for your purpose. 
Most of the data you need, can get directly from custom event attributes, same as ‘&OLD:TASK_SEQ’ which you have already used.

Hi, after burning the midnight oil, I’ve done in this way:

Change the event table to “WORK_ORDER_JOURNAL_TAB”. As the user changes the work task to released, the system creates a record here.

Like this works well.

Also set the EARLIEST_START readonly in conditional field.

As part of the process, once the user release the work task or start work, it’s not possible to return the status.

 BEGIN

  UPDATE jt_task_tab

     SET EARLIEST_START = PLANNED_FINISH

   WHERE TASK_SEQ = '&NEW:TASK_SEQ';

end;

Appreciate the help and attention of you guys.

Lopes

Prints bellow:

 

Userlevel 4
Badge +7

@lopespetro It’s good to hear you found a solution :blush:

Cheers!

Reply