ADempiere Integration With Alfresco

This Wiki is read-only for reference purposes to avoid broken links.

Status: Published

Contributed by: Kittiu, ecosoft Bangkok, Thailand.

Why Integrate ECM and ERP?

A little bit about ECM first.

• ECM is the best software for managing unstructured content like, documents, videos, web pages, etc.
• ECM (like Alfresco) is not just a software to store documents, ECM provides hell lots of service, such as better security, version control, audit history, transformation, publishing, collaboration, record management, business rules for content, workflow (Alfresco bundle with Activiti), and many more.
• In other words, ECM can manage entire life cycle of a document, from Capture -> Manage -> Deliver -> Retain -> Archive.
• In the real world scenarios, we can't really separate these two, each of the application processes in ERP, there are often textual documents supporting the purely transactional data. Product data sheets, customer correspondence, quality reports, invoices, etc.

Based on my previous life experience in commercial world, software like SAP, just to make it able to save files into an ECM system (i.e., Documentum or FileNet), as they are closed system, business have to pay a lot of money for the stupid plugin license.

Here, we see how to accomplish the same thing, EASILY and OPENLY.

See Appendix on some facts about the two integration.

How this demo works?

Adempiere already have 2 options for saving attachment files. 1) Save as binary in database 2) Save in file server. This plugin will provide the third option 3) Save to ECM (CMIS)

The usage of the new attachment will be mostly identical to the existing. But when saving the document, it will be saved as a new object of type "Adempiere Attachment" into a dropping point folder in Alfresco, and link itself back with the ObjectID rather than file path. This make it a loosely couple integration.

But as Alfresco ECM is smart, once the document is in there, we can set any business rule to automate document as we like. In this simple example, we will be creating a rule in dropping point folder, so that it automate the organization of folder structure.

1. In ADempeire, i.e., Sales Order, user attach some files
2. Files saved into the dropping point folder in Alfresco
3. Auto create relevant folders according to the document's properties
4. Move it self there for better organization of files.

Note: The plugin is created and test with Alfresco. But as it is using CMIS protocol, which is and open standard for all big name ECM, I assume it would work with others too, (i.e., Alfresco, Documentum, Filenet, OpenText, etc.)

Download

• Both source code and binary file are provided @ Bitbucket: https://bitbucket.org/kittiu/adempierecmis/downloads.
• In this tutorial, we will be using only adempiereCMIS.zip

Steps

1) Install Alfresco Community

• Download and install instruction from Alfresco Download
• Better install using the Advanced mode. You can go with the default and install everything. But do make sure ports do not conflict.
• After successfully installed, you should see 2 Alfresco Web Application running,
  1. Alfreso Explorer, http://localhost:8080/alfresco
  2. Afresco Share, http://localhost:8080/share/.

2) Test that the CMIS service using OpenCMIS Workbench

• Noted that, CMIS services will be provided through CMIS URL = http://localhost:8080/alfresco/cmisatom
• Download "OpenCMIS Workbench" from Apache Chemistry's download page.
• Just extract the zip file and run workbench.bat. Test the connection with CMIS URL, you should be able to browse through Alfresco Repository.
• Open Query dialog, and you can test CMIS query, like,

SELECT * FROM cmis:folder

• Note: Beware about SOLR Certification Expiration. Make sure you have the SOLR certificate renewed before proceed, otherwise you may face problem when do CMIS query.

3) Install adempiereCMIS plugin for Adempiere

• Extract adempiereCMIS.zip
• Install OpenCMIS libraries -> Copy OpenCMIS libraries from folder "lib" into "ADEMPIERE_HOME/lib". (You can always find the latest libraries from Apache OpenCMIS website).
• Install new plugin libraries -> Copy our plugin library, "adCMIS.jar", into ADEMPIERE_HOME/packages/adCMIS/lib folder.
• Run a migration script, located in folder "migration" against your database. This will create new fields for CMIS properties in Client window.
• RUN_silentsetup.bat (.sh), to make sure that new libraries are included in Adempiere packages.
• Login to ADempiere as SuperUser, open "Client" window and configure the connection parameter as following, we are telling Adempiere how to hook with Alfresco,

Store Attachments On CMIS: Yes
CMIS URL: http://localhost:8080/alfresco/cmisatom
CMIS Viewer URL: http://localhost:8080/share/page/document-details?nodeRef=
CMIS Root Folder: AdempiereCMIS
CMIS User: admin
CMIS Password: admin

• Now, from Adempiere side, it is now ready.

4) New object type, "ADempiere Attachment" in Alfresco

• Copy the 4 xml files from folder "alfresco_extension", into folder "ALFRESCO_HOME\tomcat\shared\classes\alfresco\extension". These XML files contains definition of new object type and its properties.
• Restart Alfresco Tomcat. New object type will be loaded.
• To test that new type is ready, login to Alfresco Explorer. Upon creating new document, you should see new option "Adempiere Attachment" for object type.

5) Create meeting point folder in Alfresco

• Lets use Alfresco Share this time.
• Login as to Alfresco Share http://localhost:8080/share. From here you will see My Dashboard view.
• Click on "Repository" icon in the top menu, this bring up the Repository view.
• Click on "New Folder" icon and create new folder name "ADempiereCMIS" under "Repository" folder.

6) Test attach file from Adempeire

• Launch Adempiere and try attach files into "Sales Order" window. Click on Attachment icon, attach some files, and click OK.
• Back and refresh Alfresco Share. Voila! Now the new files are attached and stored in Alfresco!

• Also note on the CMIS Link button in Attachment dialog.
• With a document selected, click the CMIS Link button and it will bring up the file with Alfresco Share viewer.

• As the file is now in Alfresco, you can enjoy all the benefit. You can view, edit, make new versions, start workflow, move files around, etc. The CMIS Link Button will always open up the right file with the latest version!

Let's do some more fancy stuff

The true benefit of ECM is about document automation. I won't go into too much detail on ECM features, it is a big topic.

Now, let's say, in our case, after document is loaded into ECM, we want to organize it.

As for this simple demonstration, we already have 2 additional properties from Adempeire in each document, "TableName" and "RecordID". As a document enter Alfresco, we want to automatically create document's sub folders and put it there.

(In future, we can have more properties from Adempiere like, Document Type, Document Number, Search Key, Document Owner etc. Then we can do a lot more automation based on those values).

1) Setup Business Rules to AdempiereCMIS Folder

• Login as admin to Alfresco Explorer, http://localhost:8080/alfresco
• We will be assigning a Rule for AdempiereCMIS folder, so that as a document enter it, something will happen.
• First we want to prepare a java script that will do part of the work.
  • Browse to "Data Dictionary" -> "Scripts" folder.
  • Click "Add content" and choose file = "adempiere.js" (from rule_script folder in adempiereCMIS.zip, read the code to see what it try to do)
  • Set General Properties, and finish it.
    • Name = "adempiere.js"
    • Type = "Content"
    • Content Type = "Java Script"
• Now start applying rule, let's browse to "AdempeireCMIS" folder we created earlier.
• Click "More Actions" -> "Manage Content Rules"
• On Create Rule Wizard Page
  • Step One - Select Conditions
    • Select condition = "Content of type or sub-type"
    • Set value and add = "Adempiere Attachment", ok then next.
  • Step Two - Select Actions
    • Select action = "Execute script"
    • Set value and add = "adempiere.js", OK then Next.
  • Step Three - Enter Details
    • Type = "Item are created or enter this folder"
    • Title = "Organize Adempeire Attachment"
  • Finish!

2) See the result

• In ADempiere, back to Sales Order window and delete all old attachments.
• Attach new documents again.
• Go to Alfresco Share, this time you will see that documents are organized according to the Rule! Voila!

• The magic lies in the Rule and the Script we have setup for ADempiereCMIS folder.

// Create first level folder (TableName)
var subfolder1 = space.childByNamePath(document.properties["ad:tablename"]);
if ( subfolder1== null)
   subfolder1 = space.createFolder(document.properties["ad:tablename"]);

// Move itself to first level folder
document.move(subfolder1);

// Create second level folder (RecordID)
var subfolder2 = subfolder1.childByNamePath(document.properties["ad:recordid"]);
if ( subfolder2 == null)
   subfolder2 = subfolder1.createFolder(document.properties["ad:recordid"]);

// Move itself to second level folder
document.move(subfolder2);

My Take

To me, the integration between the two is about bridging between the unstructured world of ECM (unstructured, non-linear, collaboratively flexible business process) with the structured world of ERP (structured, sophisticated, high precision).

What we have done so far was just a small demonstration. We can do a lot more in this area. And Adempiere will really be a killing sofware.

For example,

• Complex approval process, can be done in Alfresco, backed by Activiti workflow engine which is far more advanced than what can be done with Adempiere workflow. The result of approval can create a new Document in Adempiere.
• Marketing dept, use Alfresco workflow to collaborate in designing of a new product and its required parts. Once finished and approved by workflow, auto create new Product and BOM and attach itself as image for easy reference in Adempiere.
• Important accounting documents, reports and etc. in Adempiere, once freezed, auto archive as PDF and store in Alfresco in a well organized ways (i.e., by Year/Month/DocType/DocumentNo/Creator). Non ERP users can access based on their own rights, using powerful full-text/index search capability of Alfresco for auditing purposes.
• Etc...

What's Next?

Here are my random thought about features to be included in Adempiere,

• Attachment: Ability to select folder in Alfresco when do the upload.
• Attachment: Ability to select version of document (view and upload) within Attachment window.
• Archive: And CMIS option for archive.
• Auto archive document (i.e., Sales Order form) into Alfresco , when document is completed.
• Image field: Ability to link to pictures in Alfresco .
• Add more standard properties in ADempiere Document so that it will be useful when create rules.
• In the opposite way, from Alfresco , link the file back to attach with Adempiere Document.
• Workflow node in Alfresco to integrate with Adempiere.
• Workflow node in Adempiere, i.e., Complete, to trigger new workflow in Alfresco .
• Use LDAP to authenticate both side (not just using an admin to connect with Alfresco).

Have Suggestions?

If you have real business case that see the benefit of ECM + ERP, please share it here. We can use it as input to develop more features.

Appendix

Key findings (AIIM's Report - Connecting ERP and ECM, Measuring the Benefit)

• 27% of respondents who ranked themselves at a mature Level 4 or 5 for the completeness of their ERP installation only reached maturity Level 1 or 2 for their ECM practices.
• The biggest business benefit given by our respondents for linking ERP and ECM is the productivity gained by linking document and process workflows, followed by improved customer service and then knowledge sharing.
• Accounts payable is the best performing integration, followed by HR, Project Management and CRM. Invoice Number, Customer Number and Purchase Order Number are the most popular process integration links between ERP and ECM, with Contract Number as an important document integration link.
• 68% considered that linking ERP and ECM gave a better or much better return on investment than other IT integration projects.
• The biggest issue encountered by users was deciding which systems or portals to consolidate around, followed by the difficulty of convincing process owners. Mismatch of metadata standards came next.
• Amongst non-users, technical complexity and maintenance of links were the biggest concerns, followed by the political issue that ERP and ECM do not have the same project managers or sponsors. Increased licensing cost for wider client rollout was also an issue, as was divergence of ECM systems across the enterprise.
• 31% of respondents are using standard, vendor-supplied connectors to integrate ERP and ECM systems, but a total of 38% are using custom developed links - 24% in-house developed and 14% custom-developed by the vendor.
• 53% have a portal to provide single point of access to ERP and ECM content or plan to have one in the next 12 months.
• An ERP portal linking to ECM content (27%) is much more popular than an ECM portal picking up ERP content (7%), although 24% have chosen SharePoint as their portal, and may consider it to also be their ECM system.
• Within our sample, 47% have business process links and 34% have document access links, but only 12% can declare a record within ERP for migration to, or management by, a dedicated records management system and only 5% are able to implement a single-point legal hold across records in both ERP and ECM.
• SAP is the primary ERP supplier for 48% of our respondents, with 24% Oracle and 12% Microsoft.
• As regards ECM supplier, 23% are using SharePoint, with EMC/Documentum, Open Text, IBM/Filenet and Oracle/Stellent all taking around a 10-12% share.
• Asked "How would you feel about sourcing your ERP and ECM from the same supplier?" 13% already source from the same supplier and 42% agree that it could have benefits and they might consider it. 20% are committed to their existing supplier and 24% prefer the flexibility of different suppliers.
• In terms of more specific content management elements, ECM suppliers are preferred in most cases, although BPM is the most likely to be sourced from the ERP supplier, and email management from a best-of-breed supplier.