Import Media Files with IFS Connect

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 😎

6 thoughts on “Import Media Files with IFS Connect”

Add yours

David H says:

April 29, 2022 at 3:24 am

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.

1. Damith Jinasena says:
  
  May 8, 2022 at 8:06 am
  
  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?
  
  1. David Harmer says:
    
    May 9, 2022 at 10:10 am
    
    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
  2. David Harmer says:
    
    May 9, 2022 at 10:10 am
    
    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
David H says:

March 24, 2022 at 9:46 am

I did not think it was allowed to update a table in IFS using direct UPDATE command?

1. Damith Jinasena says:
  
  March 24, 2022 at 10:40 am
  
  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.