Flow Tips and Tricks: Setup event in IFS to call a Flow with all columns of the table in a few minutes

When doing integrations where IFS sends data to another system it can be quite time consuming to create all your input variables and make sure data from IFS gets to Flow. To somewhat automize this for individual tables here are some instruction on how to do the whole process in only a few minutes (using a PL/SQL Script)

How to call a Machine Flow from IFS with input from a full IFS table in less than 5 Minutes:
Prerequisites:

  1. You need admin access and PL/SQL Developer or a comparable tool.
  2. Flow 6.13 or higher
  3. IFS 10 UP4 or higher
    Steps:
  4. Create an IFS Event. Make sure to check ALL Checkboxes on either NEW or OLD, whichever you require

  1. Open PL/SQL Developer and login as admin user
  2. Open a new Test Window
  3. Copy the attached script (see comment below) into the test window
  4. In the Script assign the table Name (Same as Event Table), App_owner and whether you use the OLD or NEW parameters

grafik

  1. Run the script
  2. Go to the Output Tab in the test window. You should find something like this:

grafik

  1. Now copy the entire first line below “—Create Start Entry”
  2. Go to your workflow that you want to use and set it to edit mode
  3. Press F7 to open the code editor and also set the code to editable

grafik

  1. Scroll down a bit till you find the start Parameters entry

grafik

  1. Now paste your created string in between the Schema Tags (If you have no input yet it should be

<Schema>YourString</Schema>

  1. Commit your changes and press F7 to leave the Editor again
  2. Voilá, all your start parameters are now automatically added to your flow

  1. You can now RMB on the Flow to copy the weblink

grafik

  1. Go back to your event and click the Create Event Action Button
  2. Create a new Event Action of type REST Call (1)
  3. Copy the URL from Step 15 into the server URL (2)
  4. Copy the second part of the script into the body parameters. You will need to remove the ‘,’ after the last parameter manually (3)

  1. Add Authentication for Flow (usually BASIC Username and Password for your machine user) , enable event/event action and you now have an event that calls a flow with full input from a table.
2 Likes

Script for PL/SQL Developer:

declare

table_name_ Varchar2(100) := ‘CUSTOMER_INFO_TAB’;
attr_ Varchar2(32000);
app_owner_ Varchar2(100) := ‘IFSAPP.’;
event_old_new_ Varchar2(3) := ‘NEW’;

Cursor get_Columns is
SELECT * FROM USER_TAB_COLUMNS WHERE table_name = table_name_;
value_ Varchar2(100);
temp1_ Varchar2(100);
temp2_ Varchar2(100);
firstRun_ Boolean := true;

begin


Dbms_Output.Put_line(‘’);
Dbms_Output.Put_line(‘–Create Start Entry’);

attr_ := Null;
For rec_ in get_Columns loop
value_ := Null;
temp2_ := lower(rec_.column_name);
temp1_ := Null;
while instr(temp2_, '') > 0 Loop
temp1
:= Upper(Substr(temp2_, 1, 1)) ||
substr(temp2_, 2, instr(temp2_, '') - 2);
temp2
:= Substr(temp2_, Instr(temp2_, '') + 1);
value
:= value_ || temp1_;
End Loop;
value_ := value_ || Upper(Substr(temp2_, 1, 1)) || substr(temp2_, 2);
If firstRun_ then
attr_ := value_;
firstRun_ := false;
else
attr_ := attr_ || ', '|| value_;
end if;

end Loop;

Dbms_output.put_line(attr_);

Dbms_Output.Put_line('');

Dbms_Output.Put_line(‘–Create Rest Parameters’);

attr_ := Null;

Dbms_output.put_line(‘{’);

For rec_ in get_Columns loop
value_ := Null;
temp2_ := lower(rec_.column_name);
temp1_ := Null;
while instr(temp2_, '') > 0 Loop
temp1
:= Upper(Substr(temp2_, 1, 1)) ||
substr(temp2_, 2, instr(temp2_, '') - 2);
temp2
:= Substr(temp2_, Instr(temp2_, '') + 1);
value
:= value_ || temp1_;
End Loop;
value_ := value_ || Upper(Substr(temp2_, 1, 1)) || substr(temp2_, 2);

Dbms_output.put_line('  "' || value_ || '": "&' || event_old_new_ || ':' || rec_.column_name || '",');

end Loop;

Dbms_output.put_line(‘}’);

end;

If you want to select data from IFS you could also slighly change the script to select all columns for you and transform the Column Names to Novacura Naming Standards (You still need to remove the last ‘,’ as i was too lazy to script that so far).
Even if you only need a few columns, running the script and delete what you don’t need is usually faster than typing it. When running the select statment script you should enter the View, not the Tab.

Result:
grafik
Script:

– Created on 24.06.2021 by JOHANNES.STEGER
declare

table_name_ Varchar2(100) := ‘CUSTOMER_INFO_TAB’;
attr_ Varchar2(32000);
app_owner_ Varchar2(100) := ‘IFSAPP.’;
event_old_new_ Varchar2(3) := ‘NEW’;

Cursor get_Columns is
SELECT * FROM USER_TAB_COLUMNS WHERE table_name = table_name_;
value_ Varchar2(100);
temp1_ Varchar2(100);
temp2_ Varchar2(100);
firstRun_ Boolean := true;

begin
– Select Statement
Dbms_Output.Put_line(‘–Select Statement’);
Dbms_Output.Put_line(‘Select’);

For Rec_ in get_Columns loop
attr_ := rec_.column_name || ’ ’ || ‘"’;
value_ := Null;
temp2_ := lower(rec_.column_name);
temp1_ := Null;
while instr(temp2_, '') > 0 Loop
temp1
:= Upper(Substr(temp2_, 1, 1)) ||
substr(temp2_, 2, instr(temp2_, '') - 2);
temp2
:= Substr(temp2_, Instr(temp2_, '') + 1);
value
:= value_ || temp1_;
End Loop;
value_ := value_ || Upper(Substr(temp2_, 1, 1)) || substr(temp2_, 2);

attr_ := attr_ || value_ || '"' || ',';
dbms_output.put_line(attr_);

end Loop;

dbms_output.put_line('FROM ’ || app_owner_ || table_name_);
End;

I don’t use IFS, but I love how you showed how to edit the start parameters in the XML.

Yup, i was super happy when the code editor was introduced. In instances like this it can be quite the time saver. You can also do this for tables and records. Just manually enter them in any start step and check the needed syntax.

Hi Jostde,
Thank you for this. I m new in this field of IFS and novacura and both of them are equally important to me. you described it excellently. It will help me a lot in my further learning.
Regards,
Kshitij