One of my clients needed to automate adding the media attachments to a IFS work order. We tried to help them with a configuration which eliminated several manual steps. It was built using IFS connect and events so it’s light weight and easy to manage. Since it was a nice trick I thought of sharing the concept so it could help someone.
The approach we suggested is as follows
- name the images they want to add as media with a defined format WO_[WO_NO].png (eg: WO_12345.png )
- put the file in IFS connect file read IN folder
- IFS picks up the file and create application message
- create an Event on Application message table to create media item and attach to work order
IFS Connect Setup
We decided to create a dummy flow in IFS connect so that the application message status will be Finished. Routing address and a rule was defined so that the file which is put in the IN folder with above format will be route to the OUT folder.
Create a new routing address with destination type File and the information as below
Then create an inbound routing rule. Things to keep in mind
- Route From: NONE_XML
- Location Type: File
- Filename: add the file name pattern
- Destination address: add the file address created above
This is simply what we need from IFS Connect side for now. Put a file in IN folder to see if it works.
When the image is read into IFS, it will be saved in the FNDCN_MESSAGE_BODY_TAB. You should see the added image in the message_value column.
Now comes the fun part. Our next step is to create a media object, add the image which read into IFS and attach to the work order.
Custom event to create the media item and attach to work order
Go to the New Custom Event page and create a new Event
- Logical Unit: ApplicationMessage
- Table: FNDCN_APPLICATION_MESSAGE_TAB
- Fire when: Objects are changed (STATE)
- Select STATE, MESSAGE_FUNCTION, MESSAGE_TYPE, RECEIVER, APPLICATION_MESSAGE_ID attributes
Create a new action with type Online SQL
Add as much conditions as you could to isolate the messages in the event action. Since Application message table is centralized for all integration messages, any unexpected errors could cause errors with integrations (and printing). Therefore avoid any exceptions inside the code.
Here’s my code looks like. Note that this is created for Apps9 but I think it should work in Apps10 without a problem.
DECLARE
file_name_ varchar2(2000);
image_ BLOB;
wo_no_ VARCHAR2(100);
wo_objid_ VARCHAR2(100);
CURSOR get_msg_body IS
SELECT name, message_value from fndcn_message_body_tab
where application_message_id = '&NEW:APPLICATION_MESSAGE_ID'
and seq_no = 1;
CURSOR get_wo_objid IS
SELECT OBJID from ACTIVE_SEPARATE
WHERE WO_NO = wo_no_;
----------------------------------------
-- Sub procedure to handle media
-- Did so that the code will be simpler
----------------------------------------
PROCEDURE Create_and_attach_media(lu_ IN VARCHAR2,
ref_objid_ IN VARCHAR2,
media_item_name_ IN VARCHAR2,
image_ IN BLOB)
IS
name_ MEDIA_ITEM_TAB.name%TYPE;
description_ MEDIA_ITEM_TAB.description%TYPE;
item_id_ MEDIA_ITEM_TAB.item_id%TYPE;
attr_ VARCHAR2(32000);
info_ VARCHAR2(32000);
objid_ VARCHAR2(2000);
objversion_ VARCHAR2(2000);
library_id_ MEDIA_LIBRARY_TAB.library_id%TYPE;
library_item_id_ MEDIA_LIBRARY_ITEM_TAB.library_item_id%TYPE;
BEGIN
----------------------------------------
-- create media library and attach to record reference
----------------------------------------
name_ := media_item_name_;
description_ := 'Image received from IFS Connect';
Client_SYS.Add_To_Attr('NAME', name_, attr_);
Client_SYS.Add_To_Attr('DESCRIPTION', description_, attr_);
Client_SYS.Add_To_Attr('MEDIA_ITEM_TYPE_DB', 'IMAGE', attr_);
Client_SYS.Add_To_Attr('PRIVATE_MEDIA_ITEM', 'FALSE', attr_);
Client_SYS.Add_To_Attr('ARCHIVED_DB', 'FALSE', attr_);
Client_SYS.Add_To_Attr('LATEST_ACCESS_DATE', SYSDATE, attr_);
Media_Item_API.New__(info_, objid_, objversion_, attr_, 'DO');
item_id_ := Client_SYS.Get_Item_Value('ITEM_ID', attr_);
--update the media object from document
UPDATE MEDIA_ITEM_TAB SET MEDIA_OBJECT = image_ WHERE ITEM_ID = item_id_;
----------------------------------------
-- create media library and attach to record reference
----------------------------------------
Media_Library_API.Check_And_Create_Connection(library_id_, lu_, ref_objid_);
----------------------------------------
-- Create Media library item
----------------------------------------
info_ := NULL;
objid_ := NULL;
objversion_ := NULL;
attr_ := NULL;
library_item_id_ := Media_Library_Item_API.Get_Next_Library_Item_Id__(library_id_);
Media_Library_Item_API.New__(info_, objid_, objversion_, attr_, 'PREPARE');
Client_SYS.Add_To_Attr('LIBRARY_ID', library_id_, attr_);
Client_SYS.Add_To_Attr('LIBRARY_ITEM_ID', library_item_id_, attr_);
Client_SYS.Add_To_Attr('ITEM_ID', item_id_, attr_);
Media_Library_Item_API.New__(info_, objid_, objversion_, attr_, 'DO');
END Create_and_attach_media;
BEGIN
OPEN get_msg_body;
FETCH get_msg_body INTO file_name_, image_;
CLOSE get_msg_body;
--resolve wo_no from file_name (WO_[WO_NO].png)
wo_no_ := regexp_substr(regexp_substr(file_name_, '[^_]+', 1, 2), '[^.]+', 1, 1);
OPEN get_wo_objid;
FETCH get_wo_objid INTO wo_objid_;
CLOSE get_wo_objid;
IF wo_objid_ IS NOT NULL THEN
Create_and_attach_media('ActiveSeparate',
wo_objid_,
file_name_,
image_);
END IF;
-- do not block the application message in case of error
EXCEPTION WHEN OTHERS THEN
NULL;
END;
Now the moment of truth. Placed WO_100003.png in IN folder and it’s attached to the work order!
Please comment and share if you enjoy the post and find it useful 😎
DSJ's Blog 
Hi Damith, I got around to testing this and it all looks good but the event causes a mutating error;
ifs.fnd.base.SystemException: Failed executing statement (ORA-04091: table IFSAPP.FNDCN_APPLICATION_MESSAGE_TAB is mutating, trigger/function may not see it
How did you get around this issue? We are Apps9 UP15.
Hi David,
Sorry for the late reply. I have done this in Apps9 without getting the mutating error. In your event action, do you have any reference to FNDCN_APPLICATION_MESSAGE_TAB?
Hi. I really like the use of IFS connect here. I used the identical code to the above but changed the object it was connecting the image to.
I wiped the environment (doh) so I have to recreate it. When I do I’ll send you the event action code.
I was surprised when the mutating event message came up.
Thanks.
David
Hi. I really like the use of IFS connect here. I used the identical code to the above but changed the object it was connecting the image to.
I wiped the environment (doh) so I have to recreate it. When I do I’ll send you the event action code.
I was surprised when the mutating event message came up.
Thanks.
David
I did not think it was allowed to update a table in IFS using direct UPDATE command?
It is allowed David 😉 not the best way for other objects but for this scenario it’s the quickest way to update the blob in the MEDIA_ITEM_TAB.