We were approached by a client with a unique challenge, which involved a touch of digital transformation, Automation, and Integration. Our team successfully designed and delivered POC with high customer appreciation. We hereby wanted to share the story with everyone to share our findings and knowledge.
Customer is a manufacturing organization which receives numerous purchase orders from their clients. As customer organization has a global footprint with operations in 10+ countries, these purchase orders were of varied format, data values. At present, each location has a team of users who enter these purchase orders manually within ERP (Oracle EBS).
Customer organization wanted to automate the order intake process, to automatically create purchase order within ERP, as per order issued by their client. Following were the main considerations:
1. Create a completely automated solution to accept incoming emails with orders (as attachments) and create orders within Oracle EBS
2. Create a cost-effective yet scalable solution
3. Allow flexibility to accept multiple order templates/ structures and file types (PDF, XLS, CSV)
4. Prefer low code i.e. low maintenance
5. The solution should be cloud-ready, to be scalable and portable to other geographies
• Azure Logic Apps – for integration logic
• On-Premises Data Gateway – to integrate with on-premise Oracle EBS database
• Doc Parser – third-party document parser service
• Azure SQL Server – store incoming data and in-process buffer
• Oracle Virtual Box – host virtual machine for Oracle EBS demo instance
• Oracle EBS Vision – Oracle EBS Vision virtual machine image provided by Oracle
- Setup Virtual machine for Oracle EBS as per VM image provided by Oracle
- Install, Register and Implementation of On-Premises Data Gateway. We need to install gateway with the same azure account used for creating logic apps
- Register this gateway on azure panel and now your data gateway is ready to implement
Implementation of this proof of concept is divided into three parts:
- Set Purchase Order Template – define template for given order within DocParser
- Parse Incoming Order – Setup Azure Logic App to accept incoming email on a mailbox and process it’s attachments to parse order information; thereafter, save parsed
- Send Parsed data to Oracle – save parsed data into oracle database
Set Purchase Order Template
To set purchase order template, we need to follow the following process
1- After login in docparser.com website and we have to choose templates for which we are going to create a doc parser (in our case we are taking PO template.)
Please choose a template like Purchase Orders and enter your Parser Name and click on Click Here To Continue button.
2- After creating parser we need to add a sample document to create parsing rules for data.
Upload a sample document here and create a data parser rule(to extract data from document) like Contact . we have a default parsing rule PO Number, PO Date and Totals with PO template.
After saving parsing rules, we can see doc parser in such a way.
Now we can easily integrate doc parser in our logic app via name and key which we can easily get from doc parser web site under my account.
Parse Incoming Order
Step 1: Login to azure account and create a resource and create a logic app from here and now set your trigger event. In our case we are creating trigger on incoming emails.
Step 2: We need to add some optional parameters like has attachments, include attachments and subject line for implementing some checks.
Step 3: Now we need to add an action condition in which we will check incoming trigger response has attachment or not.
Step 4: If the condition is true then we will add a for each loop to process each attachment and upload each attachment to doc parser and will get a processed data back from doc parser.
Step 5: And we will insert processed data into SQL table (please create a SQL database server and SQL database if not created)
We have successfully created a logic app to insert attachment data into SQL table.
Send Parsed data to Oracle
Data Migration logic app from SQL table to oracle database via data gateway
Now we have to create a final logic app which will fetch data from SQL table on new data creation and insert into oracle table via on-premises data gateway.
Voila! It’s ready to run
We can check purchase orders summary after login via a superuser.
As described above, we were able to successfully complete Proof of concept in considerably less time (in fact most time was spent in setting up Oracle EBS virtual machine). The key value of this solution are as follows:
- Highly Cost-effective
- Low Maintenance
- No Code Implementation