Oracle General Ledger Integration with Hyperion Data Relationship Management

Oracle DRM and Oracle Ebusiness Suite serve two different purposes in any organisation. While DRM acts as the metadata management tool, Ebusiness suite is used to run the core business of the organisation. Traditionally, metadata, i.e. segment values, hierarchies etc are shared between DRM and EBusiness suite using text or XML files. However, the more recent versions of DRM provide a more efficient approach to achieve data integration between DRM and Oracle Ebusiness suite ( Release 12 and above, and fusion versions).

This blog post attempts to explain the process in detail to build the link between DRM and Oracle GL. Before we proceed, I’d like to list out the assumptions, and prerequisites for successful implementation of DRM-GL integration.

1) DRM is the point of entry and point of reference for all metadata. This means, new segments, hierarchies, changes to exisiting metadata should all be made in DRM, and Oracle GL would be a consumer of this metadata. Note that I am using terms metadata and data interchangeably as what is technically metadata in the larger sense will be “data” in the DRM world.

2) DRM Webservice has been successfully deployed, the right policy set applied to secure the application. Oracle have done a great job in describing how to do so here, although there are some steps which can be skipped for most purposes, according to my blog post here.

3) The developer has admininstrator access to DRM application, and also “System administrator” role in Oracle E-business suite. Its a good idea to work with someone with reasonable E-business exposure as some steps involve working with apps screens. Also, DBA’s help is needed to apply some patches on Oracle Apps, which install the necessary concurrent programs to export and import data.

4) This post assumes we are working on a brand new, “Empty” DRM application. We will be seeding data from Ebusiness suite “Vision” instance into DRM, make changes to that data in DRM, and pull it back into E-business suite. You need to modify the steps slightly to suit your specific requirements.

Just one other thing before we dive in, there is a sample DRM application that oracle are providing now. If you chose to setup that application, it comes pre-configured with most of the DRM end of things. We have written a blog post earlier with instructions on how to set it up. However, you still need to do the E-business suite side of things, deploying webservice etc.

The following picture gives an overview of the Oracle DRM GL integration process.

The very first step is to apply the necessary patches on Orace E-business suite installation. Refer to Oracle support to get the exact patch set required for your particular version of E-business suite. On our server, the following patches were applied.

Next step is to make sure the DRM webservice API was deployed properly, and has been secured with the right policy set. Oracle requires that the policy set to be used for GL integration is “Oracle/wss_username_token_client_policy”

Its a good idea to ensure the policy set is effective by testing a API call. My favorite is the “getUserNames” method.

The next few steps are about priming the DRM application to recieve data from E-business suite.

On your E-business installation, there is an application template which contains import profile, export profiles, validations, propertie definitions etc required for GL data. This needs to be loaded into the DRM application. First step is to locate the XML file on the Ebusiness sutite server.

Copy the XML file on to a machine where you can open the DRM migration client on a browser.

From a browser window, fire up the DRM migration client, and load the XML file.

Load the metadata objects by clicking on the run button.

A successful completion screen should look as below.

Verify that the metadata objects have been loaded into DRM by checking for certain GL based objects in DRM admin screen.

After verification, the next step is to customise the external tables to point to GL interface tables.

Once this is done, next step is to create an “Integration admin” user in DRM. It is very important to make sure this user account is present in Hyperion Shared Services, Oracle Weblogic server and then it needs to be added to Oracle Ebusiness suite later. If the user is from an external directory such as MSAD, or OID the user account just needs to be added in DRM. This user needs to have application manager role in DRM.

We are done with DRM settings for now. We will have to revisit DRM settings once we import the metadata seed from E-Business suite.

Login to Oracle apps with “General Ledger” responsibility and drilldown to the standard request link. We are going to execute the concurrent program which outputs the required segments in a format understood by DRM.

Select the “Initialize segment Values and Hierarchies” concurrent program and use “Operations Account” as parameter. ( We are using the sample Vision application, the readers should select an appropriate parameter based on their segment setup).

Once the job is executed successfully , the output is avaialble in the “view output” tab. Copy and paste these results into a notepad file, and save the file.

Using the “EBSValueSetImport” from DRM import profiles, import the data from the file created above into DRM. However, before doing this, we need to change the “leafedit” settings so that the data is imported correctly.

 

A successful completion of the load process creates versions with the appropriate hierarchies.

Next step is to enable the versions to be exported. Only those with “Allow Exports” set to true will be picked from DRM while loading into Oracle. So, this step is very important.

Let’s assign validations to the hierarchies now, which will ensure that the data integrity is maintained when its loaded into Ebusiness suite. Select the “Validations” category for each hierarchy and assign the necessary valiadations.

At this point, we are pretty much done with the DRM side of things, time to setup Ebusiness suite to read data from DRM, by making changes to certain profile settings.

You just need to change the wsdl URL, API adaptor URL and the username from the list. Make sure you use the same user name used earlier in DRM, and make sure it matches the case too. For some bizzare reason, username is case sensitive and it won’t work if you don’t follow the exact casing,

Next step is a little interesting as it involves storing the password for the GL integration user. This password is embedded into the webservice request by the concurrent program while loading data into Ebusiness suite. The oracle recommended way of storing this password involves executing a stored procedure on the Ebusiness suite server, but I took a short cut, and inserted it directly into the FND_VAULT. I suggest you do it the proper way when doing it in a production setting by running the following code.

sqlplus apps/apps@db @$FND_TOP/sql/afvltput.sql SQLGL GLUSER GLPASSWORD ( replace GLUSER and GLPASSWORD with username and password respectively)

The shortcut method is as below, running from SQL Developer.

Let’s add some test nodes to our hierarchies in DRM, and load them into Ebusiness suite.

We are close now, to load data into Ebusiness suite. Locate the “Load Segment Values and Hierarchies” as below.

The successful completion of this job involves two separate steps. One is the load itself, which loads data into the interface tables, and the next step is compiling the data in the interface tables to build the right segment structures. One key thing to note here is the fact that the first step, “load segment values….” should be completed with status “Normal”, any other status indicates an error and the process will NOT be complete. A good indication for successful completion is the kicking off of the “Compiling…” job subsequent to the “Load segment values..” job.

On successful completion of this job, we can move on to verifying whether the changes we made in DRM have made it through to EBusiness suite. For that, lets execute a couple of “requests” which give us the segment values, and segment hierarchies separately.

The results of the above job can be seen by clicking on “View output”, and here is section of the output showing the newly created segment value.

Next, the rollup listing.

This concludes the DRM-GL integration process, and this can now be expanded to include other segments and customized to your organizations needs.

The author is a Hyperion Solution architect based out of Sydney, Australia. He can be contacted via email or through the comments section on this blog.

Your feedback is quite valuable to us, and we’d really appreciate it if you can let us know if you like this article, or if you have any questions. We would be happy to help.

PS:- if you see the following screen while running the “load segment values….” job,

this is probably because the version is not enabled for export. Try enabling it and then run the integration again.

– See more at: http://www.trendingthoughts.com/blog/2014/10/07/oracle-general-ledger-integration-with-data-relationship-management#sthash.l0HkjjWp.dpuf

Leave a Reply