Scheduling a Custom PLSQL Script in IFS

One of the common question we get around IFS tech stuff is How to schedule something in IFS? Well, if the process you need to schedule can be trigger by calling already existing PLSQL procedure, then you are in luck. You can define a new Database Task and schedule the execution of that task. But there are limitations to this such as limited number of supported data types for parameters and a method cannot have multiple definitions.

If you can’t find a suitable PLSQL method, one way to proceed is to write a custom PLSQL package and a procedure which comply to IFS Database Task requirements and create a Database Task based on that custom PLSQL procedure. Downside of this approach is the maintainability of the code since ‘someone’ has to take care of it separately.

Another alternative for scheduling is using IFS Data Migration. This requires some advanced knowledge about Data Migration and you can define the Migration Job to call the server method and schedule the job execution.

What I will discuss here is a much simpler approach for scheduling a PL/SQL script using IFS Events.

There are two types of events in IFS:

• Application Defined Events – Execution is built in within PLSQL logic. IFS controls when the event is triggers
• Custom Defined Events – Based on database triggers. You can create a Custom Event for a database table and CRUD functions triggers the event and then calls the actions which are enabled for that event.

Application defined events are developed and maintained by IFS RnD. Custom defined events as the name suggests is a part of the extendibility of IFS and customer can create, import/export, version control them inside IFS.

In our approach, we are doing a small ‘hack’ to the triggering of Custom Defined events and execute via a schedule task..

First step is to Create the custom event and action to define the script we need to execute. Navigate to the New Custom Event window and create an Event. Logical Unit and the Table can be anything.

Important! Do not enable the Fire When conditions. It will create a database trigger and execute event actions unnecessarily with transactions on the table.

Next step is to define the custom script. Press the Create new action link on right top corner of the page and select the Action type as Execute Online SQL. You can define the SQL statement for your script in the box. If you need to pass any values or evaluate conditions, best way is to create a custom Logical unit and store them. In this example, I’ve used a simple code to send an email.

Now we are all set to continue with scheduling. For packaging and versioning purpose, we can add the event and event action to an Application Configuration Package

Next step is the scheduling of the script. First, we need to a new Database task. Navigate to New Database Task window.

Keep the parameter list blank and save.

Press the Create New Schedule link on top right corner of the page. this will bring to the Scheduling screen. Fill up the parameters for EVENT_LU_NAME_ and EVENT_ID_ of what we created in above.

Now everything is setup to schedule execution of your script. Sit back and relax…😎 In due time, your script will execute and do the job!

Hope you find this article useful. Please comment your thoughts and share if you think it will help someone too!