Question

Event action "Execute Online SQL" in IFS Cloud


Userlevel 5
Badge +11

Hello,
I just learned that the action for "Execute Online SQL" events will be removed in the next versions of IFS Cloud.

In relation to this, I have 2 questions: 
- For all the events already created for our customers in IFS Cloud, how will this happen? We will have to abandon our actions already created? 
- For our customers in APPS10, we have a lot of EVENTS. How will the migration from APPS10 to Cloud happen?

Thank you in advance.

 


16 replies

Userlevel 7
Badge +31

Hi @TheoB,

Please have a look at following documentation regarding best practices for Custom Events:

https://docs.ifs.com/techdocs/22r1/040_tailoring/225_configuration/010_config_best_practice/#best-practices-for-custom-events

This document mentions that the action type ‘Workflow’ is supposed to replace the Execute Online SQL action type. 

The action type Workflow allows us to call BPA workflows that handle data enrichment, validation etc. This is the main replacement for the Execute Online SQL action type.

IFS recommends that the Execute Online SQL action type is NOT used, going forward. The recommendation is to use BPA Workflows to solve more advanced requirements.

Note that IFS intends to remove the Execute Online SQL event action type in a future update.

 

Hope someone from Product Development will provide more information regarding your specific questions. 

Hope this helps!

 

Userlevel 5
Badge +11

Hi @Charith Epitawatta,
Thank you for this information.
What interests me most is to know what will happen to the EVENTS that are already created.
Will they be totally unusable? Will it be necessary to redevelop everything? 
And also how will the migration from APPS10 to Cloud be done for the events?…

Let's hope someone goes through this topic!
Thanks in advance.

Userlevel 3
Badge +4

@TheoB Yes this is something in the plans. We will take note of this question and we will make sure to communicate the migration path well in advance before we remove this event action in a future Feature Update. There is no specific target release for this as of now.

Userlevel 5
Badge +11

Hi @ashdlk,

Thank you for this information.
So if I understand correctly, we should absolutely not develop EVENT in PL/SQL because we will have to delete them at some point and manually rebuild the EVENT for the "Workflow" action type?

Userlevel 3
Badge +4

@TheoB In general, the use of PL/SQL will be eventually deprecated for more configurable data extraction using Query Designer and Workflows etc. While you may still continue to use PLSQL event actions, in due future the same purpose would be more effectively fulfilled with other tools. I can’t confirm that you will have to manually migrate/rebuild what you already have, but I wanted to express the general direction we are heading. 

Userlevel 7
Badge +28

The very significant problem though is neither the Query Designer or Workflows are up to the task of replacing the functionality that is possible with PL/SQL execution.  We are in the process of upgrading from Version 9 to 22R1 (probably will be on a later version before live), but we have already hit the limits of BPA Workflows in every area we need to use them, and haven’t even begun to use the Query Designer because the two evaluations we’ve done of the product thus far indicate it is not even useful enough to start using for the replacement of any of our current functions or searches.

Add in the lack of User Profiles and Advanced SQL Searches within each page and we have a serious gap that at the moment prevents going live in IFS Cloud.  We are taking our users such a large step backwards with the loss of functionality that we are doing a significant level of work to find alternatives to replace the previous functionality rather than attempting to use the provided (and in the case of BPA paid for) tools to do the work.  Release to market of the Cloud version was at least a year in advance of where it should have been given the current functionality.

Userlevel 2
Badge +4

We have no plans to deprecate online SQL event actions through at least 2024. However, for new implementations, we recommend Workflow instead. For upgrades, where feasible, we recommend replacing online SQL event actions with Workflows.

Why Workflows?

Online SQL presents many problems. If we were to build IFS Cloud, from scratch today, there's no way we'd consider a feature like this. It's insecure and problematic to our evergreen ambition. It requires extensive knowledge of our schema, and can bypass our business rules and validations.

Workflows address these problems, and enable customers to optimize the system themselves.

Why Now?

23R2 represents a great leap forward for Workflows. In the past, our APIs severely limited the use cases we could support. In 23R2 however,  we've enabled the new Entity Service APIs. Where most of our previous APIs were obtuse and procedural, the Entity Service APIs are transparent and granular. With them, you can orchestrate transactions in the same way online SQL allows you to do. While still enforcing our validations and business policies.

Userlevel 7
Badge +28

One of the problems is that IFS presumes that their business rules and validations are 100% useful for every customer.  Having some flexibility or adaptability or yes in some cases, the ability to bypass them is entirely necessary to run a business efficiently.  In many instances, there are cumbersome tasks or routes to finish the transaction that causes problems for users to the point they either aren’t doing them right or just don’t do them.

Executing SQL event actions becomes a very necessary transaction even as mature as V9 was, let alone with Cloud where all of the tools were stripped out.

Userlevel 7
Badge +18

I would like to emphasize that there is not always possible to replace a PL/SQL event action with a Workflow. If you have complex things/things in business critical flows/things in performance critical flows in PL/SQL it might be impossible or not suitable to do that in a Workflow.

From documentation:

This will require analysis to determine which can be replaced using another approach such as a Workflow, Extend on the Outside or Extend on the Inside; and which can be removed, perhaps because new features in IFS Cloud render them redundant.”

https://docs.ifs.com/techdocs/23r1/040_tailoring/225_configuration/150_config_best_practice/#best_practices_for_custom_events

Userlevel 2
Badge +4

One of the problems is that IFS presumes that their business rules and validations are 100% useful for every customer.  Having some flexibility or adaptability or yes in some cases, the ability to bypass them is entirely necessary to run a business efficiently.  In many instances, there are cumbersome tasks or routes to finish the transaction that causes problems for users to the point they either aren’t doing them right or just don’t do them.

Executing SQL event actions becomes a very necessary transaction even as mature as V9 was, let alone with Cloud where all of the tools were stripped out.

 

Hi Shawn,

Thanks for the insight. Data quality and management is critical for IFS Cloud and ensuring the value of new and existing features we bring to our customers. It’s imperative today that data be in a state expected by the system, and critical to where we’re going with BI & analytics as well as AI and Machine Learning. 

Can you give me some examples of use cases you believe you need SQL event actions to address needs that can no longer be met because “tools were stripped out”? That would help us ensure that we’re not missing something. Thank you!

Userlevel 2
Badge +4

I would like to emphasize that there is not always possible to replace a PL/SQL event action with a Workflow. If you have complex things/things in business critical flows/things in performance critical flows in PL/SQL it might be impossible or not suitable to do that in a Workflow.

From documentation:

This will require analysis to determine which can be replaced using another approach such as a Workflow, Extend on the Outside or Extend on the Inside; and which can be removed, perhaps because new features in IFS Cloud render them redundant.”

https://docs.ifs.com/techdocs/23r1/040_tailoring/225_configuration/150_config_best_practice/#best_practices_for_custom_events

 

Hi Tomas,

It’s true, it’s not always possible to replace SQL event actions with a Workflow. Nor, given the nearly unlimited flexibility of SQL event actions, will it every likely be. However, there are costs to those SQL event actions such as:

  1. There’s no guarentee they will work from release to release. Underlying schema changes could break them or severely impact their performance.
  2. They’re not changed to meet changing behaviors or expectations in the application.
  3. They require extensive knowledge of our data schema and therefore can’t be used by our customers.
  4. There are all sorts of security risks associated with them.

This is a good example of a feature that we would not consider adding to IFS Cloud if it didn’t exist, for the reasons above. At the same time, the unlimited flexibility makes it difficult to take away. Hence why we haven’t deprecated it yet.

As I mentioned above though, with 23R2, we now support the Entity Service APIs. Combined with the Query Designer, there shouldn’t be many reasonable business cases where previously you used SQL event actions that can’t be met now. If there are, please reach out and let’s talk through them to make sure we have a solution in our plans going forward!

 

Thank you!

Userlevel 7
Badge +28

Can you give me some examples of use cases you believe you need SQL event actions to address needs that can no longer be met because “tools were stripped out”? That would help us ensure that we’re not missing something. 

 

The problem is that you can’t build in all of the needs for each customer by using rigid tools, rather having a wide open function like SQL event actions allows tailoring IFS to the business needs.

Our examples will be unique, but may give an idea:

1.

Generating a unique system serial number based on custom fields added to the customer order header.  The combination of an order class (machine type) and embedded counters allow the user to simply pick yes or no to create a new system ID, but the work is all done with a SQL event.

2.

New machine systems always include an installation and require the creation of a service call on the service side.  This can only be done after a functional and serial object are created.  There is a custom RMB (V9) that we have difficultly recreating in Cloud that will take the above system ID and the customer details based on the customer ID selected and create a functional and serial object in sequence so that the creation of all of these details is a single click for the user.  Again, all done by sending SQL behind the scenes.

3.

The pricing mechanism we use is unique in that it uses a Net Amount for the order (entered by order entry and determined by sales) and then by using a single click, the algorithm works out the proper discount for each item that is needed to match the Net amount to the discounted Gross Amount.  For orders with 50+ lines, it would be extremely tedious and error prone for users to apply the discount manually.  They would never get it right becuase some items aren’t allowed to be discounted while others get the full discount, this in the end determines what the real discount is because it isn’t as simple as Gross-Net.  

These are a few of the big ones, but there are actually more than a dozen.

 

With the SQL event actions and sending SQL, we can reduce hundreds of clicks to do repetitative tasks to just 1 or 2 clicks and ensure that there can’t be errors as the decision making is taken out of the users hands and logic dictates the outcome. 

By removing deeply embedded tools that have been prolifically used by customers for years on the more stable (and able versions of IFS) and not replacing them with an equivalent function (BPA is definitely not up to the task yet), we constantly run into ‘upgrading’ to IFS Cloud is actually a big step backwards for user experience because it actually makes more work for the user.

More clicks, more areas for mistakes to occur, etc.

 

 

Userlevel 1
Badge +4

We are looking towards an 10->Cloud upgrade project as well with the exact same issue of having large amounts of PLSQL code blocks in custom events and custom menus.

Did anyone find a best practice document or just a good way to approach the upgrade of these knowing that PLSQL block functionality will be decommissioned?

Userlevel 1
Badge +7

Good morning everyone, does anyone know when the “Execute Online SQL” action will be removed from IFS Cloud?

We are in Apps10, is there any plan to remove this functionality from that version of IFS?

What is the recommendation for customer events in Apps10 instead of “Execute Online SQL” to ensure that they are easy to port to Cloud in the future?

Userlevel 7
Badge +28

They won’t remove the functionality from Apps 10 because it is already there.

They never developed an equivalent for Cloud and didn’t understand how customers might use the software and relied on BPA to be the solution.

IFS hasn’t provided any solution to this issue, so there is no path to make moving to Cloud easy - thus the reason after more than three years since project kickoff - we still aren’t live on Cloud.

Userlevel 2
Badge +8

I have regular meetings with IFS R&D regarding the new workflow functionality.

CURRENTLY IT IS NOT A MATURE SOLUTION YET.

I would not recommend moving to workflows anytime soon.

While IFS stay purposefully vague with when sql would be depricated, they won't remove it until at least 24R2 and if you would be on that version, you still would have 23 months to move to Workflows.

My expectation is that they won't remove it for a long time, if ever.

Performance wise, the new tooling is not anywhere close to SQL standards.

Reply