Solved

Update Custom field using Event

  • 14 September 2020
  • 18 replies
  • 212 views

Userlevel 1
Badge +3

Hi Community

 

I have created a Persistent custom field in Sales Basic Data → Salesman to enter Sales manager of each salesman. 

 

I have created another custom field in Customer order → Misc Order info tab and I want to update Sales manager’s id into that custom field using an event. 

 

i created an event to achive that but i am getting no result. Customer order doesn’t fire any error too. 

Can you let me know where the issue is ? 

 

 

DECLARE
  SalesM_ VARCHAR2(20);
  pragma autonomous_transaction; 

BEGIN

  SELECT CF$_SALES_MANAGER
    INTO SalesM_
    from SALES_PART_SALESMAN_CFV
    WHERE salesman_code =  '&NEW:SALESMAN_CODE';

  IF CUSTOMER_ORDER_API.Get_Authorize_Code('&NEW :ORDER_NO') = 'IWT' THEN
    UPDATE Customer_Order_Cft 
       SET CF$_S_MANAGER = SalesM_
     WHERE rowkey = (SELECT rowkey
                          FROM Customer_Order_tab 
                         WHERE order_no = '&NEW:ORDER_NO');
  END IF;

END;

 

icon

Best answer by anmise 17 September 2020, 08:55

@Tj12

There are challenges in the way you are achieving this. But I am struggling to understand why you are doing it. Is it because you want to store info on the current sales manager when the order was released?

 

 Hi Hans 

Yes. Business want save this data and populate these into reports. If i use reference fields I assume that i won’t be able to see history data if manager got changed 

 

Maybe you could trigger it on the CustomerOrderHistory instead. Below code works, but you’d obviously have to add/tweak some conditions either in the event action

Or in the code:

DECLARE
info_ VARCHAR2(4000) := NULL;
attr_ VARCHAR2(4000);
attr_cf_ VARCHAR2(4000);

Cursor get is
select objid, ifsapp.SALES_PART_SALESMAN_CFP.Get_Cf$_Sales_Manager(SALES_PART_SALESMAN_CFP.Get_Objkey(salesman_code)) salesmanager
from CUSTOMER_ORDER
WHERE order_no= '&NEW:ORDER_NO';

BEGIN
IF '&NEW:HIST_STATE' = 'Released' AND '&NEW:MESSAGE_TEXT' = 'Released' THEN

FOR rec_ in get LOOP

Client_SYS.Clear_Attr(attr_);
Client_SYS.Clear_Attr(attr_cf_);
Client_SYS.Add_To_Attr('CF$_SALES_MANAGER', rec_.salesmanager, attr_cf_);
IFSAPP.Customer_Order_Cfp.Cf_Modify__(info_, rec_.objid, attr_cf_, attr_, 'DO');
END LOOP;
END IF;

END;

 

View original

18 replies

Userlevel 5
Badge +9

you have a space between NEW and :ORDER_NO

Userlevel 5
Badge +9

If still an issue - we would need to know whether it is a reference-type field or just a text field. 

That affects whether you need to use CF$_SALES_MANAGER or CF$_SALES_MANAGER_DB

Userlevel 1
Badge +3

If still an issue - we would need to know whether it is a reference-type field or just a text field. 

That affects whether you need to use CF$_SALES_MANAGER or CF$_SALES_MANAGER_DB

 

Hi Paul, Thanks for commenting, 

 

I removed the space but still the same issue.  Both custom fields are text fields. 

Userlevel 5
Badge +9

ok, you will need to take it apart to see where the problem is...

If the select statement was not finding any data, you would be seeing a “no data found” error there.  Aside: I would recommend using an explicit cursor here instead- the “no data found” error is not good for users to see.

Next i would remove the IF condition and see if it then works; that would imply that the IF condition is not being met.

Finally, you need to confirm whether the event action as a whole is running.  My recommendation would be to drop this line into the script and see if the error appears at that point -if the error shows up, it means the event action ran and reached that point.

        Error_SYS.Record_General('PurchasePart','You are here');

(If you don’t see the error, this tells you the event/action is not being triggered).

Userlevel 3
Badge +5

I agree with Paul - make sure the custom event is firing.  If it is, I wonder if you can directly run an update statement to a DB table from inside a custom event.

If that’s the problem, you need to use the standard modify statement.  Something like

     objid_ := := '&NEW:ROWKEY';
     Client_SYS.Clear_Attr(attr_cf_);
     Client_SYS.Clear_Attr(attr_);
     Client_SYS.Add_To_Attr('CF$_S_MANAGER', SalesM_, attr_cf_);
     Customer_Order_Cfp.Cf_Modify__(info_, objid_, attr_cf_, attr_, 'DO');      
 

Sample variable declaration

    info_         VARCHAR2(2000);
    objid_        VARCHAR2(2000);
    attr_         VARCHAR2(32000);
    attr_cf_      VARCHAR2(32000);
 

 

Userlevel 1
Badge +3

ok, you will need to take it apart to see where the problem is...

If the select statement was not finding any data, you would be seeing a “no data found” error there.  Aside: I would recommend using an explicit cursor here instead- the “no data found” error is not good for users to see.

Next i would remove the IF condition and see if it then works; that would imply that the IF condition is not being met.

Finally, you need to confirm whether the event action as a whole is running.  My recommendation would be to drop this line into the script and see if the error appears at that point -if the error shows up, it means the event action ran and reached that point.

        Error_SYS.Record_General('PurchasePart','You are here');

(If you don’t see the error, this tells you the event/action is not being triggered).

 

Hi Paul, 

Excellent guidance, Now i know my event is firing when i release the order.  But yet I am unable to update the field and I am getting below error

 

 

 

below is the code i used 

 

DECLARE
  pragma autonomous_transaction; 
  SalesM_ VARCHAR2(20);

  Cursor getobj is 
  select objid from CUSTOMER_ORDER 
  WHERE salesman_code =  '&NEW:SALESMAN_CODE';

    info_         VARCHAR2(4000);
    objid_       VARCHAR2(4000);
    attr_         VARCHAR2(4000);
    attr_cf_    VARCHAR2(4000);

BEGIN

         OPEN getobj;
         FETCH getobj INTO objid_;
         CLOSE getobj;
 

  SELECT CF$_SALES_MANAGER
    INTO SalesM_
    from SALES_PART_SALESMAN_CFV
    WHERE salesman_code =  '&NEW:SALESMAN_CODE';

     Client_SYS.Clear_Attr(attr_);
     Client_SYS.Add_To_Attr('CF$_S_MANAGER', SalesM_, attr_cf_);
     Customer_Order_Cfp.Cf_Modify__(info_, objid_, attr_cf_, attr_, 'DO');  

END;

 

Userlevel 1
Badge +3

I agree with Paul - make sure the custom event is firing.  If it is, I wonder if you can directly run an update statement to a DB table from inside a custom event.

If that’s the problem, you need to use the standard modify statement.  Something like

     objid_ := := '&NEW:ROWKEY';
     Client_SYS.Clear_Attr(attr_cf_);
     Client_SYS.Clear_Attr(attr_);
     Client_SYS.Add_To_Attr('CF$_S_MANAGER', SalesM_, attr_cf_);
     Customer_Order_Cfp.Cf_Modify__(info_, objid_, attr_cf_, attr_, 'DO');      
 

Sample variable declaration

    info_         VARCHAR2(2000);
    objid_        VARCHAR2(2000);
    attr_         VARCHAR2(32000);
    attr_cf_      VARCHAR2(32000);
 

 

Hi J 

Thanks for commenting 

As i replied to paul Event is firing. but unable to update the field. 

Userlevel 5
Badge +9

yeah, that autonomous transaction thing is maybe not supported in IFS Events.  I have not used it.  

I suspect the next issue you might hit will be “table is mutating, trigger may not see it”.

Userlevel 3
Badge +5

@Tj12 - I think your cursor statement might be the problem.  

Cursor getobj is 
  select objid from CUSTOMER_ORDER 
  WHERE salesman_code =  '&NEW:SALESMAN_CODE';

Is retrieving all customer order rows with the salesman code.  You need the 1 record that matches your order.  But, I think &NEW:ROWKEY on customer_order_tab is the Object ID in customer_order (you’d have to look at the view definition to confirm - or just try it).

Userlevel 4
Badge +9

@Tj12

There are challenges in the way you are achieving this. But I am struggling to understand why you are doing it. Is it because you want to store info on the current sales manager when the order was released?

 

Userlevel 6

Could you not just add a reference field to display the sales manager on the customer order instead of setting it in a persistent field?

Userlevel 1
Badge +3

@Tj12

There are challenges in the way you are achieving this. But I am struggling to understand why you are doing it. Is it because you want to store info on the current sales manager when the order was released?

 

 Hi Hans 

Yes and Business want to populate manager automatically. They are too lazy to select and enter it. 

 

Userlevel 1
Badge +3

Could you not just add a reference field to display the sales manager on the customer order instead of setting it in a persistent field?

Hi Anmise 

 

Can. but business want to fill the manager automatically when they save the order or release the order 

Userlevel 4
Badge +9

The relationship between sales manager and salesman is normally in the organisation diagram (who does the salesman refer to). When you make the sales manager cf in sales basic data, you make data where it does not belong. It could/will be in conflict with the definition of your organisation.

Can you not lookup the sales manager in the organisation diagram?   

Userlevel 6

@Tj12

There are challenges in the way you are achieving this. But I am struggling to understand why you are doing it. Is it because you want to store info on the current sales manager when the order was released?

 

 Hi Hans 

Yes. Business want save this data and populate these into reports. If i use reference fields I assume that i won’t be able to see history data if manager got changed 

 

Maybe you could trigger it on the CustomerOrderHistory instead. Below code works, but you’d obviously have to add/tweak some conditions either in the event action

Or in the code:

DECLARE
info_ VARCHAR2(4000) := NULL;
attr_ VARCHAR2(4000);
attr_cf_ VARCHAR2(4000);

Cursor get is
select objid, ifsapp.SALES_PART_SALESMAN_CFP.Get_Cf$_Sales_Manager(SALES_PART_SALESMAN_CFP.Get_Objkey(salesman_code)) salesmanager
from CUSTOMER_ORDER
WHERE order_no= '&NEW:ORDER_NO';

BEGIN
IF '&NEW:HIST_STATE' = 'Released' AND '&NEW:MESSAGE_TEXT' = 'Released' THEN

FOR rec_ in get LOOP

Client_SYS.Clear_Attr(attr_);
Client_SYS.Clear_Attr(attr_cf_);
Client_SYS.Add_To_Attr('CF$_SALES_MANAGER', rec_.salesmanager, attr_cf_);
IFSAPP.Customer_Order_Cfp.Cf_Modify__(info_, rec_.objid, attr_cf_, attr_, 'DO');
END LOOP;
END IF;

END;

 

Userlevel 1
Badge +3

Hi  Guys 

Finally was able to figure it out after doing some tweaks in the code. 

 However it still give active autonomous transaction detected error but after putting COMMIT command into the event action, I didn’t get any errors

Any Advice ? 

 

Userlevel 6

Hi  Guys 

Finally was able to figure it out after doing some tweaks in the code. 

 However it still give active autonomous transaction detected error but after putting COMMIT command into the event action, I didn’t get any errors

Any Advice ? 

 

 Did you try the previously suggested trigger on the history table?

Userlevel 1
Badge +3

Hi  Guys 

Finally was able to figure it out after doing some tweaks in the code. 

 However it still give active autonomous transaction detected error but after putting COMMIT command into the event action, I didn’t get any errors

Any Advice ? 

 

 Did you try the previously suggested trigger on the history table?

 

Hi Anmise

Just tried with your suggestion and it works perfectly without mutating error.  This will work for me definitely. 

Thanks a lot for your advice and Suggestion.  

Reply