Oracle EDM Cloud Service Integration With EBS can be done using a middleware. In this post, we will see how we can achieve this using oracle integration cloud.
The use case is whenever a new Chart of account value is added to oracle EDMCS the same value should be correspondingly updated in oracle EBS.
Why is Oracle EDM Cloud Service Integration With EBS required and how it helps?
This integration from EDM to EBS is useful for EBS customers who are looking to leverage the value of using Enterprise Data Management to manage and govern COA segment values and associated hierarchies for EBS as well as other Financial Systems such as Hyperion on-premise (HFM, Planning, Essbase, etc.), Oracle Cloud EPM (EPBCS, FCCS, PCMCS, Tax Reporting, etc.), BI and other non-Oracle applications.
EDM provides a central, business-driven change management foundation to create, model, manage and validate enterprise data values and hierarchies such as Account, Legal Entity, Cost Center, Dept, Location, Project, and others, and then synchronize these values and hierarchies to any application.
With is EDMCS can co-exist with Oracle Ebusiness Suite and EDM can manage the COA values and hierarchies for both EBS and Cloud Financials. EDM can also be the collaborative platform for COA redesign and mapping between the EBS COA and the new Cloud Financials COA.
EDMCS export of hierarchies can be consumed by cloud Financials but as of date, we do not have the same feature for EBS to consume the export data.
so to integrate EDM Cloud with EBS for COA updates, the approach would be sequenced as below:
- Retrieve the dimension information (ID) for the EDMCS application from which COA metadata will be exported.
GET <your EDMCS Server URL>/epm/rest/v1/applications
Example: GET https://edmcs.oracledemocloud.com/epm/rest/v1/applications
- Run an export of the dimension(s)
POST <your EDMCS Server URL>/epm/rest/v1/dimensions/<your dimension ID from the application metadata in step 1>/export/download
Example: POST https://edmcs.oracledemocloud.com:443/epm/rest/v1/dimensions/9i835d62h761a2- 4aec033t /export/download
- Verify and confirm the status of export
GET <your EDMCS Server URL>/epm/rest/v1/jobRuns/<the job ID generated as a response of step 2>
Example: GET https://edmcs.oraclecloud.com:443/epm/rest/v1/jobRuns/4-4f1d-af52-5cfd5aeaec033t-361058ff8
- Finally download the export to a flat file (csv in our case)
GET <your EDMCS Server URL>/epm/rest/v1/files/temp/<job ID from step 2>?fileName=<your file name>
Example: GET https://edmcs.oraclecloud.com:443/epm/rest/v1/files/temp/4aec033p-3610-4f1d-af52-5cfd5ae58ff8?fileName=dimension.csv
5. Read the file in an OIC For loop record by record
6. For every read in step 5 insert the record in EBS DRM Hierarchy interface (GL_HIERARCHY_INTERFACE)
For this, you will need to call a custom PL SQL package in EBS which does the insert. Either you call this via DB adapter or the EBS adapter.
7. Similarly Read segments File
8. for each segment record insert in EBS segments interface
insert into GL.GL_DRM_HIERARCHY_INTERFACE ( value,parent_value,depth)
insert into gl.gl_drm_segvalues_interface
9. Check and confirm your records in EBS tables GL_HIERARCHY_INTERFACE GL_SEGVALUES_INTERFACE
10. Finally Call DRM Standard import process with Oracle EBS Adapter.
apps.GL_DRM_INTEGRATION_PKG.gl_drm_import_process
Create a Scheduled Integration Process in OIC to achieve the above steps:
Read the export file
https://<edmcs domain>/epm/rest
use the payload response from postman.
/epm/rest/v1/dimensions/{dimensionId}/export/download
{
"fileName" : "IDC.zip"
}
Response Payload
{
"links" : [ {
"rel" : "results",
"href" : "https://(edmcs domain):443/epm/rest/v1/jobRuns/130cd7d0-d25c-4448-81dc-16c51613c26f"
} ]
}
Use File adapter to Get File Download And read
REST API for Oracle Enterprise Data Management Cloud Service – Download a temporary file
Create a custom concurrent program in EBS to call the DRM import Package.
So finally the OIC process will look something like this.
For more information about EDMCS Rest API, please visit the following links: https://docs.oracle.com/en/cloud/saas/enterprise-data-management-cloud/edmra/edmcs_url_structure_rest_api_resource.html
