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!

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.

@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.

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?

@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. 

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.

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.

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.

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 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!

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!

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.

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?

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?

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.

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.

Any news on how this solution is maturing at all @JoDe or @ShawnBerk 

This is also a strong concern for me as the Solution Architect within our company, potentially preventing a migration (currently on Apps10) to any version of Cloud that has actually deprecated that functionality.

We use a LOT of PLSQL Events, to do a LOT of very important transactions with complex logic and filters that I don’t feel BPA workflows is capable of doing as of yet ?

Here’s a few examples (not exhaustive at all, some might be doable, or there might be workarounds, or you might say we shouldn’t have done that in the first place, but that’s not the point, the point is it was done and now it’s stuck with us):

1. We have a complex PLSQL Custom Package to handle auto emailing of PDF reports printed within the report archive, with specific logic defined within the method(s) to determine the path of action, e.g. An invoice PDF is created in the archive, it will retrieve the AR Customer Record Comm Method, and send an email to the customer automatically if it is set to output email, if the AR customer record is a valid email, if the user that generated the print is set as a Credit Controller for the Company the invoice is registered against, and if the invoice amount is not zero.

2. Several custom Methods added on top of the Change Request/Change Order modules base functionality, that prevents a user from modifying or deleting a change request (even though they are granted the underlying modify__ or remove__ APIs) unless they are its creator, the “CR Responsible”, or one of their direct or indirect supervisors (the method uses a recursive function to perform tree analysis and branch “up” from the request creator up to CEO effectively and checks if the person clicking is part of that list or not)
    
3. Various notification email events with complex logic to identify “to” and “cc” email recipients
    
4. Various transactional events around manufacturing and Shop Orders that call different APIs based again on complex logic checking details based on who is triggering it, the details of the inventory part being manufactured, the day of the week it’s being clicked on, that sort of stuff
    
5. Various transactional events around Preposting to automatically populate Preposting without the user having to select it manually, where again the results of which codeparts to populate and with which value is a complex logic of different use cases that check a lot of criteria in various logical units to make a determination

Again this list is not exhaustive AT ALL, we have over 150 Online SQL Event Actions (although I’m sure SOME could be reworked as BPA workflows, they’re not ALL super complex). As we have a Cloud migration in our roadmap hopefully in the next couple years due to end of support for Apps 10, this is really keeping me up at night, I’ll be honest.

While I understand IFS wants to reduce the amount of knowledge of the data modeling and architecture that is required to be able to customize the solution, it also feels like they are punishing the people who HAVE developped that knowledge and are no longer capable of enabling the most benefits from their expertise.

A simple question I might be called to ask here that I didn’t find easily in the BPA documentation is, is there any plan for BPA to be able to call a Custom PLSQL API Method at any point in the workflow, effectively. If there is no such plan, I don’t see how I would rework a lot of these events ?

Another similar drawback (although not related to events in particular) is the loss of the ability to create Advanced SQL Saved Searches in the web client, where again knowledge of the underlying APIs was rewarded with very powerful abilities.

Thanks for reading and looking forward to your input :)

Simple answers to your questions because there is no simple IFS version to answer them.

is there any plan for BPA to be able to call a Custom PLSQL API Method at any point in the workflow

Not that IFS has committed to, in fact the opposite really.  They see no compelling reason to do so no matter the examples given.

loss of the ability to create Advanced SQL Saved Searches in the web client

Yeah, this is a huge setback to the super user group who can actually create some of these on their own or at least an analyst can create them quickly and deploy to a group (Buyer, Sales Admin, Engineer, etc).  The only solution here is lobbies and that means less variation unless you have fulltime people just to manage it.

We are approaching 4 years into the project and really have made zero progress the last 18 months.  Each new version promises to get us closer, but in the end misses the mark. 

The sad reality is that we started this upgrade endeavor with the intention of pulling in all of the groups (sales, service, engineering) into the same group that manufacturing and accounting already lived in.  Lately, we’re entertaining implementing Salesforce to replace the entire sales side, alongside other similar third-part solutions because of the huge setbacks of Cloud vs IEE.

The entire team is skeptical of the actual ability to go live at this point and we’ve started splitting groups off to other projects.  That ought to give you a real idea of the feasibility.

I will say that we have a variation of every one of the scenarios you mention which both indicates the shortcomings of the base solution as well as the robust ability to overcome those shortcomings with the online SQL actions.

I’d say more….but it would probably get black marked like some of my previous complaints…..

@SimonTestard  I would recommend just not going with workflows for at least another 2 years. IFS cannot force people to start using this tooling and depending on who you ask it MAY OR MAY NOT BE REPLACING ONLINE SQL.

What I've done in some cases in regards to complex queries is to make a quick report and call that API for certain results and you can also pass a parameter. 

I've moved to a higher upgrade this year because support was running out and I found that there were hard commits and rollbacks added to API's.

Since the workflow engine in it's current state does not support those functionalities, it means we HAVE TO REDO THEM IN SQL. And with every update something could break and we would have to rebuild workflows to SQL. I'm assuming that these functionalities are only done with release updates, but there's no guarantee there either.

Absolutely mindboggling.

Bottom line, do not go down this slippery slope as of YET or anytime soon really.

Thank you both @ShawnBerk  and @JoDe 

@Andrew Lichey is there anything you can say with regards to the examples provided above ? Willing to have a private chat if you’d like, but unless you guys allow BPA workflows to call custom API methods, I’m not sure how feasible it’d be to replicate in BPA ?

I’m willing to share custom API code as examples if required, to give you an idea of how our events interact with PLSQL Logic and you can tell me whether you think BPA is mature enough to be able to recreate those events through it ?

Note that I do understand your concerns, specifically around security and all that, I’m not discounting that, Online SQL is EXTREMELY powerful and lets you do a LOT of stuff, in fact it can act as a backdoor to many things, but with the flexibility it brings it also has allowed businesses to heavily customize and automate their workflows with complex logic, and removing that ability is a real hindrance to being able to upgrade to IFS Cloud at all.

Which is too bad cause I actually really like IFS Cloud in terms of general UI and performance, but I can’t really sell that to the end users if it means they suddenly have to spend a lot more time doing things that were automated before.

I’m in a really tough spot where company leadership wants to upgrade to Cloud due to end of support for Apps 10, but the business end users would create pushback so immense the change management would effectively become insurmountable and get us stuck like @ShawnBerk says he is, between a rock and a hard place.

I’ll also say that it feels a bit weird because Custom Actions (The replacement for Custom Menu in IEE) that you configure against Projections/Entities already DO let you call custom API Methods.

If you’re letting people create custom API Methods to be called by the click of a custom button in the web client, it seems inconsistent that you would not let users effectively “automate” the click of that button through a trigger.

@ShawnBerk , Looking at this:

https://docs.ifs.com/techdocs/24r1/040_tailoring/500_business_process_automation/080_workflow_examples/#e-mail_example

Specifically this part :

And This part:

Doesn’t that seem to indicate that one could create one or multiple custom entities where their sole purpose is to hold the calls to the custom PL/SQL Package Methods (Instead of COMMAND_SYS.MAIL), and then use a workflow to call them ?

Obviously it would require the workflow to somehow get/parse/build the parameters from the event, but I assume surely this should be doable, like getting the event NEW and OLD Values and use them as some parameters to parse into the API Call parameters?

I am very new to workflows so I have little confidence in what I’m saying but at a glance it SEEMS possible, am I missing anything here ?

Ok I’ve played a little with this and one thing that it seems to do, not sure if this is intended, but I can only get base values parsed to the workflow initialization parameters

My event looks like this:

But when I trigger the event, the watch tool returns these variables,

I modify say the name and description and work hours per day at once, I get this through the Watch Tool

{"FinancialProjectExist":false,"Description":"New Desc","EVENT_ACTION_ROWKEY":"1D845E4973726772E06365A3F20A8542","ProjectId":"1000","ROWKEY":"1D845FCC68EF6888E06365A3F20A36D3","CustomerId":"1283661","Manager":"6","Name":"New Name","ifsBpaExecutionUserId":"ifsadmin","ifsBpaCurrentProjectionName":"ProjectDefinitionHandling","Objstate":"Initialized","Objgrants":null,"ETag":"W/\"Vy8iQUFBVnZHQUJnQUFCRVMvQUFBOjIwMjQwNzE4MTY0NjUzIg==\"","ifsBpaCurrentInputEntityTypeName":"Project","WorkDayToHoursConv":9.0,"ifsBpaProjectionOperation":"UPDATE","PlanFinish":"17/08/2024"}

So, I’m getting:

• the NEW attributes that I’ve requested (Description, Manager, PlanFinish, CustomerID)
• Some Keys I’ve not even requested (Project ID, Event Action Rowkey, etc.)
• Some attributes I have NOT requested but I changed during the transaction (WorkDayToHoursConv, Name)

However, I am not getting the OLD attributes I’ve requested

Again, not sure if I’m missing something very simple here, anyone know if you can parse the OLD Value from an attribute to a workflow when triggering the workflow from a custom event action?

Ok if that helps anyone I managed to “trick” IFS into sending the OLD attributes anyways, by defining them as custom attributes in the event itself.

I don’t know why it does this, maybe it has something to do with the fact the BPA workflow engine will not allow the same variable name twice (it says that in the documentation), although that makes little sense because even if I ONLY try to parse the old attributes, I don’t get them at all (and there’s no duplication happening there).

Anyways, this is how you can get them to parse:

by doing it this way, I then get the following in the workflow execution variables:

{"old_description":"dfdfd","FinancialProjectExist":true,"Description":"dfdfdzzzzzzzzzzzzz","EVENT_ACTION_ROWKEY":"1D845E4973726772E06365A3F20A8542","ProjectId":"1000","ROWKEY":"1D845FCC68EF6888E06365A3F20A36D3","CustomerId":"1283661","Manager":"6","Name":"dffdzzzz","ifsBpaExecutionUserId":"ifsadmin","ifsBpaCurrentProjectionName":"ProjectDefinitionHandling","Objstate":"Initialized","Objgrants":null,"ETag":"W/\"Vy8iQUFBVnZHQUJnQUFCRVMvQUFBOjIwMjQwNzE4MTgwODMxIg==\"","ifsBpaCurrentInputEntityTypeName":"Project","ifsBpaProjectionOperation":"UPDATE","PlanFinish":"17/08/2024"}

So you can see I now can get both the old attribute and the new one in the execution variables.

With this, will I be able to call an API, we’ll see, I need to do some more testing.

Alright so having done further testing, I can then call an API (I’ve tried with error_sys.record_general, by adding method as a custom action from a projection, then having the workflow call that custom action), which seems to work fine.

I have not tested with custom API methods as I don’t have any deployed in the cloud environment available to me, but I assume it should be doable as long as the utility package is properly deployed and the method is available in the list of package/methods given to you when defining the custom action against the projection.

The projection configuration reads from

Which itself reads from table Custom_Action_Methods_MV, which I believe gets populated when you deploy a package/refresh the dictionary cache

One thing is that you can’t really easily create an attr_ to parse to a method where the method then uses client_sys.get_item_value, you’d need to create a “call” method first with the parameters as separate arguments and then have that call method build the attr and have it call the actual transactional method using the attr_, if that’s what you want to do.

I mean it’s THEORETICALLY probably possible to create the attr_ manually through process enrichment variable declaration, but it’d be a pain as you’d need to build it fully manually as you can’t use method client_sys.add_to_attr method, instead you have to build the variable using javascript.

So all in all, a custom event set a workflow should be able to call a custom API with complex logic happening through the PL/SQL codeline of the Utility Package Method itself rather than trying to make extremely complex logic in the workflow itself, which for a developer is probably much harder to actually troubleshoot, at least for people experienced in PL/SQL.

@ShawnBerk @JoDe does that help at all ? Any use case I’m missing that you couldn’t do using the steps I’ve just described? Note I clearly haven’t tried to replicate all my events, this is a big undertaking for sure, but it seems at least THEORETICALLY doable on paper?

Yes, this all seems doable and theoretically possible, but if I remember right, the hangup comes in trying to take information from one projection and use that to create other records in another table where you are outside the normal projection for that record entry.  If there are business rules in place within that other record/table, the record will fail to create.

I’m going off memory and I’m not the developer so I may not have this 100%, but I think that is where we hit the roadblock.  Reading info works fine, updating records within the bounds of the existing record that was created using the projection/table/business logic works ok, but new records at the header and detail level in another module was a no go.  Think taking customer and system information from the customer order and using that to create service records for the system within the service module.  Something that is dozens of transactions and commits if you do it manually on the projection doesn’t work.