Want a No-Nonsense Integration? Make Sure It’s Done with the Right Middleware Tool
There are a lot of moving parts when it comes to migration and integration projects. Each one has its own unique roadmap that requires specialization so every project is a new experience for me. This is what makes my job rewarding as I always have a new challenge to solve for the client. In this blog post, I am going to share with you the approach taken with a recent project where I (along with my team) replaced a client’s outdated legacy ERP system with Oracle Cloud ERP and used Dell Boomi as the middleware platform.
Our client is in the pharmaceutical industry. As with any growing business, they realized their legacy ERP system was falling short for their business needs. The procurement and self-service requisition processes had no governance, which caused bad data and out-of-synch system data. There were other system limitations as well. The business needed a fully integrated global ERP solution.
Apps Associates helped the client to assess various middleware technologies and after the assessment phase, the client decided to use Dell Boomi as the middleware platform to pull, transform, implement business rules and load transactional data in Oracle Cloud ERP.
Development approach for Oracle Cloud ERP implementation
The target system, Oracle Cloud ERP, exposes webservices for all the data objects that integrate with the external systems. We identified the webservices needed for loading the necessary data objects in the target system and tested it using Postman and Google REST Clients. We established working payloads to create and/or update all the data objects within the scope of the implementation.
We implemented the following modules for the client:
- Financials: General Ledger, Accounts Payable, Accounts Receivable, Fixed Assets (FA), CASH/ Purchasing and self-service requisitions
- Supply Chain Management: Planning Central, Manufacturing, Inventory, complex global transfer pricing, and COGS (Cost of Goods Sold) accounting configurations
Mapping Sessions
We conducted extensive mapping sessions with the client’s business users to build mappings between the source and the target systems for the integrations in the scope. We gathered and validated the business rules identified for each integration and documented the rules in the technical specifications document.
Testing Sessions
We built Dell Boomi processes for happy path scenarios, then tested and reviewed them with business users and functional leads before working on all the use cases and test scripts defined for the integrations. We expanded the Dell Boomi processes to include error handling and email notifications before UAT (User Acceptance Testing). We have subjected the integrations through rigorous testing cycles before UAT and production deployment.
Oracle Cloud ERP Integration Architecture Diagram
The client has business relationships with multiple 3PLs (3rd Party Logistics) and CMOs (Contract Manufacturing Organizations). The 3PLs and CMOs are the source systems that send transactional data to be loaded in Oracle Cloud ERP. The 3PLS send Customers, Account Receivables Transactions, and Inventory Movement transactions on a daily basis to be loaded into Oracle Cloud ERP.
Here is one challenge we addressed. These multiple 3PLs and CMOs each post data to their own SFTP servers. The client has the option to connect to each of the 3PL SFTP servers, and pull data to process and load in Oracle Cloud ERP. This approach requires maintaining separate SFTP connections in Dell Boomi for each of the 3PLs. (An expensive approach as it would end up consuming the Dell Boomi connections as one connection each per 3PL and CMO.) We worked with each of the 3PL technical teams and convinced them to post data on to the client’s SFTP server instead. Now, the client can create just one SFTP connection and consume data from all the 3PLs.
Design Considerations in Dell Boomi:
We have used the following Dell Boomi capabilities to design the integrations.
To combine all the responses from one web service when it’s triggered multiple times, use Message Shape in Dell Boomi and check “combined documents into single message”.
Use Document Property if multiple documents are available in the source or if the need is to identify id of a particular document. Otherwise, use process property.
When loading required data into Document cache to retrieve when needed, use Document Cache Shape and provide Unique Index to retrieve data when required.
To calculate rebate and sales allowance amount based on country, create user-defined functions and write groovy script.
To trigger sub-processes once for each country based on currency codes, split components into multiple documents based on currency code and trigger sub-processes once for every currency code.
Multiple SOAP Webservices? Try HTTP connections
This technical implementation involved dealing with multiple SOAP (Simpifled Object Access Protocol) Webservices. We have an option to use SOAP Client connection in Dell Boomi for each SOAP Webservice. The SOAP client requires an independent connection for each end point. This approach would consume a lot of Dell Boomi connections. Instead, we created HTTP connections to work with Oracle Cloud Webservices. With HTTP connections, you only need a unique connection for each unique host URL. The same HTTP connection could be used for multiple end points. This approach reduced the number of connections needed for the implementation from 12 to 4.
Sometimes you need a SOAP Client Connection
We ran into a webservice that would send response payload back encoded in Base 64 format. The Dell Boomi process was not able to correctly decode the response payload coming back from the webservice when connected using HTTP Client option. After multiple unsuccessful attempts to leverage the HTTP client connection instead of SOAP Client connection, we created a SOAP Client connection for this specific Webservice.
Reusable Processes
We also built the following reusable components that can be used across multiple integrations.
File Validation Utility : All integrations involved at the client were file based integrations and we built a generic File validation utility which validated the file name format and file format.
Email Notification Utility : We built a generic Email notification process to send Success, Error, or Info Email notifications with attachments where necessary.
Oracle Organization IDs Utility : We built a generic process to fetch Legal Entity Ids, Business Unit IDs, Organization IDs, and Inventory Organization IDs as required by various integrations.
Conclusion
To summarize, by implementing the above approach we had a very successful go-live of the integrations mentioned and we set the stage for them to add on other integrations as needed in the future. I would be interested to hear about your experiences on your integration projects that are similar in nature to this one. Leave your comments below and let’s continue the conversation. Also, check out our other blog posts at https://appsassociates.com/.