Jostde
October 13, 2022, 4:08pm
1
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:
You need admin access and PL/SQL Developer or a comparable tool.
Flow 6.13 or higher
IFS 10 UP4 or higher
Create an IFS Event. Make sure to check ALL Checkboxes on either NEW or OLD, whichever you require
Open PL/SQL Developer and login as admin user
Open a new Test Window
Copy the attached script (see comment below) into the test window
In the Script assign the table Name (Same as Event Table), App_owner and whether you use the OLD or NEW parameters
Run the script
Go to the Output Tab in the test window. You should find something like this:
Now copy the entire first line below “—Create Start Entry”
Go to your workflow that you want to use and set it to edit mode
Press F7 to open the code editor and also set the code to editable
Scroll down a bit till you find the start Parameters entry
Now paste your created string in between the Schema Tags (If you have no input yet it should be
<Schema>YourString</Schema>
Commit your changes and press F7 to leave the Editor again
Voilá, all your start parameters are now automatically added to your flow
You can now RMB on the Flow to copy the weblink
Go back to your event and click the Create Event Action Button
Create a new Event Action of type REST Call (1)
Copy the URL from Step 15 into the server URL (2)
Copy the second part of the script into the body parameters. You will need to remove the ‘,’ after the last parameter manually (3)
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
Jostde
October 13, 2022, 4:09pm
2
Script for PL/SQL Developer:
declare
table_name_ Varchar2(100) := ‘CUSTOMER_INFO_TAB’;
Cursor get_Columns is
begin
–
attr_ := Null;') > 0 Loop := Upper(Substr(temp2_, 1, 1)) ||') - 2); := Substr(temp2_, Instr(temp2_, '') + 1); := value_ || temp1_;
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') > 0 Loop := Upper(Substr(temp2_, 1, 1)) ||') - 2); := Substr(temp2_, Instr(temp2_, '') + 1); := value_ || temp1_;
Dbms_output.put_line(' "' || value_ || '": "&' || event_old_new_ || ':' || rec_.column_name || '",');
end Loop;
Dbms_output.put_line(‘}’);
end;
Jostde
October 13, 2022, 4:40pm
3
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).
Result: Script:
– Created on 24.06.2021 by JOHANNES.STEGER
table_name_ Varchar2(100) := ‘CUSTOMER_INFO_TAB’;
Cursor get_Columns is
begin
For Rec_ in get_Columns loop') > 0 Loop := Upper(Substr(temp2_, 1, 1)) ||') - 2); := Substr(temp2_, Instr(temp2_, '') + 1); := value_ || temp1_;
attr_ := attr_ || value_ || '"' || ',';
dbms_output.put_line(attr_);
end Loop;
dbms_output.put_line('FROM ’ || app_owner_ || table_name_);
I don’t use IFS, but I love how you showed how to edit the start parameters in the XML.
Jostde
October 14, 2022, 2:33pm
5
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,
Jostde
January 19, 2023, 8:45am
7
Came back to copy my script and noticed that some of the required underscores have been removed when pasting. Try to repost the corrected version: `
declare
table_name_ Varchar2(100) := 'NOVACURA_LOG_CLT';
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;
`
