Hi,

This can be done in many different ways. For example, you can build a client or integration that does this using our APIs, but I think you want something that is as simple as possible.

The simplest way can think of that does not require that much work would be something like this:

1. Arrange things such that all files has the inventory part number somewhere in the file name
2. Import all files in the File Import screen.
3. Write a SQL script that loops over all the newly imported documents, extracting the inventory part number and connecting the document to the right part. Perhaps also clear the inventory part number from the document title now.

It takes some knowledge in SQL, PL/SQL and IFS' data model, of course, and access to the database from SQL*Plus or a similar tool.

Or, now that I think of it, perhaps a custom event can do it too. As each new document is imported, a custom event could trigger, doing what the script above would do (connecting the document to a part).

Good luck!

/Mathias
 

Hi,

I decided to give this a go, to test my theory. Seems this approach works well.

I hope this might help others who need to do similar things in relation to importing documents.

The building blocks are:

- A custom event that fires on inserts/new records in the EdmFile LU (table = edm_file_tab) and where we make four of the keys available for event actions.
- An event action connected to the custom event, of type Execute Online SQL.
- The File Import screen. Each file that is imported must have the part number in parentheses, like this: My document (12345).docx. Here, 12345 would be the part number used when connecting the document. The site (see CONTRACT= in the code below) is hard coded, and probably can be in most cases. The LU, InventoryPart is also hard coded.

Here is the code for the event action:

DECLARE

   doc_class_ doc_issue_tab.doc_class%TYPE := '&NEW:DOC_CLASS';

   doc_no_    doc_issue_tab.doc_no%TYPE    := '&NEW:DOC_NO';

   doc_sheet_ doc_issue_tab.doc_sheet%TYPE := '&NEW:DOC_SHEET';

   doc_rev_   doc_issue_tab.doc_rev%TYPE   := '&NEW:DOC_REV';

   title_     doc_title_tab.title%TYPE;

   file_name_ VARCHAR2(100);

   part_no_   VARCHAR2(100);

   FUNCTION Get_File_Name RETURN VARCHAR2 IS

   BEGIN

      RETURN REGEXP_SUBSTR (title_, 'File ((A-Za-z0-9() ]+) imported', 1, 1, NULL, 1);

   END Get_File_Name;

   FUNCTION Is_Imported RETURN BOOLEAN IS

   BEGIN

      IF title_ LIKE 'File%imported' THEN

         RETURN TRUE;

      END IF;

      RETURN FALSE;

   END Is_Imported;

   FUNCTION Get_Part_No RETURN VARCHAR2 IS

   BEGIN

      RETURN REGEXP_SUBSTR (file_name_, '\(((0-9]+)\)', 1, 1, NULL, 1);

   END Get_Part_No;

   PROCEDURE Log (string_ IN VARCHAR2)

   IS

   BEGIN

      DBMS_OUTPUT.Put_Line (string_);

   END Log;

   PROCEDURE Get_Title IS

   BEGIN

      SELECT title INTO title_

      FROM doc_title_tab

      WHERE doc_no = doc_no_;

   END Get_Title;

BEGIN

   Get_Title;

   IF Is_Imported THEN

      Log ('imported document found');

      file_name_ := Get_File_Name;

      Log ('  file_name_ = ' || file_name_);

      part_no_ := Get_Part_No;

      Log ('  part_no_ = ' || part_no_);

      IF part_no_ IS NOT NULL THEN

         Doc_Reference_Object_API.Create_New_Reference(doc_class_, doc_no_, doc_sheet_, doc_rev_, 'InventoryPart', 'CONTRACT=70^PART_NO=' || part_no_ || '^');

      END IF;



   END IF;

END;

Again, above the site is hard coded (CONTRACT=70). Modify it to match your setup. For other LUs you need to figure out the key ref format yourself. If you decide to put the part number using some other "format" of the file name, you need to modify the Get_Part_No function above to match that. Right now, the part number is supposed to be a number, with or without hyphens in it (12345 and 12-34-5 are supported.)

I have tried to place most of the logic in the code behind helper procedures or functions to make the main block of code as easy to understand as possible.

Good luck!

/Mathias
 

Dear Mathias,

Thanks a lot for the guidance. It worked though after some modifications.

But all thanks to you!  I took reference from your idea of using File Import feature.

I used it to attached multiple documents to multiple Employees under Employee Files screen.

The steps which I followed :-

1. Create an Event for Lu_Name = EdmFile and Table = EDM_FILE_TAB ::: Same as suggested by you
2. Create an Event Action for new insert of record in this Table with Action Type - Execute Online SQL:: Same as suggested by you.
3. I changed the SQL code as per my requirement:

===========

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;  

   doc_class_ doc_issue_tab.doc_class%TYPE := '&NEW:DOC_CLASS';
   doc_no_    doc_issue_tab.doc_no%TYPE    := '&NEW:DOC_NO';
   doc_sheet_ doc_issue_tab.doc_sheet%TYPE := '&NEW:DOC_SHEET';
   doc_rev_   doc_issue_tab.doc_rev%TYPE   := '&NEW:DOC_REV';

CURSOR get_data IS 
SELECT 
       S.DOC_CLASS,
       S.DOC_NO, 
       S.DOC_SHEET,
       S.DOC_REV,
       REGEXP_SUBSTR (S.TITLE, '\(( 0-9]+)\)', 1, 1, NULL, 1) emp_no
FROM  IFSAPP.DOC_ISSUE_REFERENCE s
WHERE S.DOC_CLASS = doc_class_      
      AND S.DOC_NO    = doc_no_ 
      AND S.DOC_SHEET = doc_sheet_
      AND S.DOC_REV   = doc_rev_
;

BEGIN 
   FOR i in get_data
     LOOP  
       
       BEGIN
         SAVEPOINT A;
             
          IFSAPP.DOC_REFERENCE_OBJECT_API.Create_New_Reference(i.doc_class, i.doc_no, i.doc_sheet, i.doc_rev, 'CompanyPerson', 'COMPANY_ID=100^EMP_NO=' || i.emp_no || '^');
          
    COMMIT;
     
          doc_class_ := NULL ;
          doc_no_    := NULL ;
          doc_sheet_ := NULL ;
          doc_rev_   := NULL ;
          
      EXCEPTION
        WHEN others THEN
          ROLLBACK to A;

    END;

   COMMIT;
   END LOOP;
END;

====

Is totally solved my purpose.

Thanks a lot again !!!!
 

DECLARE

   doc_class_ doc_issue_tab.doc_class%TYPE := '&NEW:DOC_CLASS';

   doc_no_    doc_issue_tab.doc_no%TYPE    := '&NEW:DOC_NO';

   doc_sheet_ doc_issue_tab.doc_sheet%TYPE := '&NEW:DOC_SHEET';

   doc_rev_   doc_issue_tab.doc_rev%TYPE   := '&NEW:DOC_REV';

   title_     doc_title_tab.title%TYPE;

   file_name_ VARCHAR2(100);

   part_no_   VARCHAR2(100);

   FUNCTION Get_File_Name RETURN VARCHAR2 IS

   BEGIN

      RETURN REGEXP_SUBSTR (title_, 'File ((A-Za-z0-9() ]+) imported', 1, 1, NULL, 1);

   END Get_File_Name;

   FUNCTION Is_Imported RETURN BOOLEAN IS

   BEGIN

      IF title_ LIKE 'File%imported' THEN

         RETURN TRUE;

      END IF;

      RETURN FALSE;

   END Is_Imported;

   FUNCTION Get_Part_No RETURN VARCHAR2 IS

   BEGIN

      RETURN REGEXP_SUBSTR (file_name_, '\(((0-9]+)\)', 1, 1, NULL, 1);

   END Get_Part_No;

   PROCEDURE Log (string_ IN VARCHAR2)

   IS

   BEGIN

      DBMS_OUTPUT.Put_Line (string_);

   END Log;

   PROCEDURE Get_Title IS

   BEGIN

      SELECT title INTO title_

      FROM doc_title_tab

      WHERE doc_no = doc_no_;

   END Get_Title;

BEGIN

   Get_Title;

   IF Is_Imported THEN

      Log ('imported document found');

      file_name_ := Get_File_Name;

      Log ('  file_name_ = ' || file_name_);

      part_no_ := Get_Part_No;

      Log ('  part_no_ = ' || part_no_);

      IF part_no_ IS NOT NULL THEN

         Doc_Reference_Object_API.Create_New_Reference(doc_class_, doc_no_, doc_sheet_, doc_rev_, 'InventoryPart', 'CONTRACT=70^PART_NO=' || part_no_ || '^');

      END IF;



   END IF;

END;

Dear Mathias,

Thanks a lot for the guidance. It worked though after some modifications.

But all thanks to you!  I took reference from your idea of using File Import feature.

I used it to attached multiple documents to multiple Employees under Employee Files screen.

The steps which I followed :-

1. Create an Event for Lu_Name = EdmFile and Table = EDM_FILE_TAB ::: Same as suggested by you
2. Create an Event Action for new insert of record in this Table with Action Type - Execute Online SQL:: Same as suggested by you.
3. I changed the SQL code as per my requirement:

===========

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;  

   doc_class_ doc_issue_tab.doc_class%TYPE := '&NEW:DOC_CLASS';
   doc_no_    doc_issue_tab.doc_no%TYPE    := '&NEW:DOC_NO';
   doc_sheet_ doc_issue_tab.doc_sheet%TYPE := '&NEW:DOC_SHEET';
   doc_rev_   doc_issue_tab.doc_rev%TYPE   := '&NEW:DOC_REV';

CURSOR get_data IS 
SELECT 
       S.DOC_CLASS,
       S.DOC_NO, 
       S.DOC_SHEET,
       S.DOC_REV,
       REGEXP_SUBSTR (S.TITLE, '\((b0-9]+)\)', 1, 1, NULL, 1) emp_no
FROM  IFSAPP.DOC_ISSUE_REFERENCE s
WHERE S.DOC_CLASS = doc_class_      
      AND S.DOC_NO    = doc_no_ 
      AND S.DOC_SHEET = doc_sheet_
      AND S.DOC_REV   = doc_rev_
;

BEGIN 
   FOR i in get_data
     LOOP  
       
       BEGIN
         SAVEPOINT A;
             
          IFSAPP.DOC_REFERENCE_OBJECT_API.Create_New_Reference(i.doc_class, i.doc_no, i.doc_sheet, i.doc_rev, 'CompanyPerson', 'COMPANY_ID=100^EMP_NO=' || i.emp_no || '^');
          
    COMMIT;
     
          doc_class_ := NULL ;
          doc_no_    := NULL ;
          doc_sheet_ := NULL ;
          doc_rev_   := NULL ;
          
      EXCEPTION
        WHEN others THEN
          ROLLBACK to A;

    END;

   COMMIT;
   END LOOP;
END;

====

Is totally solved my purpose.

Thanks a lot again !!!!