Skip to main content

I decided to create this conversation as I don’t see this issue addressed clearly in other areas of the IFS Community. Although it is alluded to in other questions, no real answer is provided by IFS that has been satisfactory (or maybe it is but I could not find it).

The question is simple: Why has IFS fully restricted the execution of any IFSAPP API’s to the user accounts given to Customer’s IT departments to connect to Oracle (that is, IFSINFO and IFSDBREADONLY)? What alternatives does IFS provide to Customer’s IT departments to perform some tasks that are SAFE that were entirely possible in APPS10?

I would like to expand a bit on what I mean by SAFE API calls. In the past, IFS Consultants maintained that the state of IFS Database Access to Customer’s IT Departments was presenting a risk, as users (with the IFSAPP password) could do whatever CRUD operations they wanted against the base tables CIRCUMVENTING IFS API USAGE and that was very risky.

Fast forward to IFS Cloud architecture and now it seems that the pendulum has moved in the opposite way. In my view, this concern of Customer’s IT departments manipulating their data has made IFS fully restrict not only CRUD operations against base tables but also a very handy programmatically safe way to perform actions against IFS using PL/SQL scripts, that is, the very same API’s the IFS Cloud UI uses to manipulate data.

By removing such execute permission to ALL ACCOUNTS that a Customer can potentially use in Oracle, they have effectively taken away from customer’s the ability to perform any safe bulk operations. I know that the Migration Tool would allow you to bulk update or insert data, but that doesn’t provide for the same flexibility a PL/SQL Script would provide for those who are experienced PL/SQL Developers.

In addition to that, when it comes to executing an IFS API in a loop in APPS10 you could do something like this, which is safe (because you are effectively not doing anything against the data outside the real of IFS’s own APIs)

FOR rec_ IN (SELECT * FROM /*Table*/ p /*WHERE p.*/)
LOOP
IFSAPP./*API*/./*CALL*/;
END LOOP;

Now in IFS Cloud the answer may be to create a Migration Job, but frankly (and after using migration Jobs for many years) it is very clear that this requires a lot of configuration, is error prone, and is way more time consuming (we are talking about hours of elbow greasing parameters and settings) that the 4 lines of code above can achieve in 1 minute.

Am I missing something? is there an easy alternative to address this situation?

@msurasky-price 

every view in IFS is reachable via an API so you can get quite far with these

“entity integration apis”  https://docs.ifs.com/techdocs/23r2/040_tailoring/300_extensibility/240_integration/500_entity_service_apis/

 

Any specific scenarios you miss? As in these times of Copilot / ChatGPT

a python script is very quickly written to do some bulk actions? 

So I think it comes down to the scenario you are looking at?  

You can always resort to custom_events to do some bulk actions if you really want to go the PL/SQL way.  

I agree having write access to the db was really nice but if we are honest, its not the future in any SAAS or ERP to have direct db access.

 

 

Thanks @kvbe,

Before I reply to anything you have said, I’ll do some homework and investigate this “Entity Integration APIs” :)

Once I get the chance to review this documentation, I’ll reply.

As for Scenarios, and to give you one example (so it is a bit more “tangible” and you understand better what issues I’m precisely talking about) let me share some pseudo-code that was created (in the APPS10 world) to address a situation where the Customer needed to update the Inventory Part’s “Safety Lead Time” in hundreds of parts.

BEGIN

FOR inventory_part_to_update_rec IN (
(WHATEVER_SELECT_CRITERIA_YOU_NEED_HERE
) LOOP
p0_ := NULL;
p1_ := inventory_part_to_update_rec.OBJID;
p2_ := inventory_part_to_update_rec.OBJVERSION;
client_sys.Clear_Attr(p3_);
client_sys.add_to_attr(name_ => 'SAFETY_LEAD_TIME', value_ => inventory_part_to_update_rec.NEW_SAFETY_LEAD_TIME, attr_ => p3_);
p4_ := 'DO';
BEGIN
IFSAPP.INVENTORY_PART_PLANNING_API.MODIFY__( p0_ , p1_ , p2_ , p3_ , p4_ );
END;
END LOOP;
END;

Why did you mention Phyton in particular?

I’m not an IFS Developer, my background is from the relational-DBA-role world (SQL Server, Oracle) so as you can imagine SQL scripts (or PL/SQL) are the most comfortable thing for me, but if there really was an advantage with Phyton, I don’t think I would object to learn it in order to achieve things I can no longer do with SQL scripts.


@msurasky-price 

I understand its not what you want,

but these type of queries/modifications you can still via a migration job if you want to stay in the SQL/DB world? 

IF you are really adventurous you can try BPAs. But thats a bit of a hit and miss sometimes. Depending on what you would like to do.

 

For power users it is annoying but we need to adapt a bit.

The end result is that you do not need to open up your full database over VPN and that you can start to use platforms like Boomi / Windmill / N8 to do your one off scripts.

 

There is a lot to unpack here…  but thank you very much for your perspective!

Like I said, I will have to sit and learn new tooling/languages to accomplish these same tasks now, and I’m not against that, I only want to get a good understanding on what the direction is.

I have the impression that there could have been a better way to find some compromise that would  have reduced/eliminated risk without fully closing the door on SQL API execution, but these are architectural decisions that are beyond the scope of this discussion, I’m more into practical solutions to overcome the issue than long and pointless philosophical discussions about architecture… LOL! :)


Reply