Yesterday I had a problem with the HZ_ORIG_SYSTEM_REF_BO_PUB package in the E-Business Suite (R12).

To enter this site you have to login to Oracle Metalink. I found out that it also requires that it should be the flash-version of metalink (the classical logon wouldn't do).

In this site you can find all the latest and greatest stuff about the technicals of E-business Suite. For example the latest versions of the package-sources.

Looking for the HZ_ORIG_SYSTEM_REF_BO_PUB would deliver me the latest version (that would be 12.0.6 at the moment). of the package source.

I found that in the assign_orig_sys_ref function the following assignment exists:

x_orig_sys_rec.owner_table_name := HZ_REGISTRY_VALIDATE_BO_PVT.get_owner_table_name(p_orig_sys_obj.object_type);

What this does is that it fills the table_name with a translated object type.

I found this translation:

• PARTY_SITE: HZ_PARTY_SITES
• ORG, PERSON, ORG_CONTACT: HZ_PARTIES
• ORG_CUST, PERSON_CUST, CUST_ACCT:HZ_CUST_ACCOUNTS
• CUST_ACCT_CONTACT: HZ_CUST_ACCOUNT_ROLES
• CUST_ACCT_SITE: HZ_CUST_ACCT_SITES_ALL
• PHONE, EMAIL, TLX, WEB, EDI, EFT, SMS: HZ_CONTACT_POINTS
• LOCATION: HZ_LOCATIONS
• CUST_ACCT_SITE_USE: HZ_CUST_SITE_USES_ALL

So if you want to relate a party_site to an original reference number, then you have to pass 'PARTY_SITE' for object_type as parameter.

Actually we are quite some time "under way" (Dutch saying) in 2009.
We started this year with a new website. Take a look www.darwin-it.nl. And we're proud of the result. We hope you like the design and that this new site is a good starting point for you to learn about our services that we can offer you.

Slowly we transform the layout of our weblog to get it inline with the website layout. Today's transformation is a start. But in the end it's about the content and I hope we can offer you many thoughts and experiences the rest of this year. Because it also means that we learned a lot new things ourselves.

And that is also inline with 2009 being declared as the Darwin-year: together get smarter, to get the IT- (and especially the Oracle-) Community fitter for survival.

The last few years I had to think about using the EBS-Adapter for several times. The E-Business Suite adapter is delivered with the SoaSuite, earlier with BPEL PM 10.1.2. When running the adapter wizard in jDeveloper under the Technology Adapters you find the wizard for the Applications- Adapter (EBS Adapter). The EBS-adapter is actually a special sauce over the database adapter (for the public-api's and the Open Interface Tables) and the AQ-adapter (for the Business Event System). The EBS adapter wizard makes it easier to introspect the interface possibilities of EBS. It downloads the Integration Repository (IRep) and makes it introspectable. So you can browse through the IRep and choose the interface you need and the Adapter wizard generates the correct artefacts.

If you choose a Public API, a TCA-Person-API for example, then it will generate a wrapper package and accompanying Object Types. The API's often use record types, and the database-adapter does not support them. It does support Object Types perfectly. Even with complex hierarchies.

The generated pl/sql are syntactically correct and function properly. However the naming of the object types and the procedures are generated using an quite obscure algorithm. The name of the package is changeable (you'll be asked for the name) but the name of the object types are generated. The names of the functions that convert the recort-types to object-types and visa versa are generated using a name like pls_to_sql, where is a sequence number. I found that if multiple object types are used, the generated functions for a particular object type do not neccessarly have the same sequence number. Also I found with earlier releases that there can be name-collision between object types from API's that are in name closely related (like party-site and party-site-use).

If the EBS-Adapter was for free, I would tend to accept these side-affects. Then it is to Oracle to support the correct naming and behaviour. But unfortunately it is far from free. You have to pay a significant amount for the use of the EBS Adapter. And that is where I found the story become strange. Because a customer pays a quite respectable license fee for the E-Business Suite. Also for the SoaSuite. Then both products need a database and since the customer probably use it not only for strict EBusiness Suite use, they should have a Full Use Database license. Maybe even for the only use of SoaSuite you should have a full-use license.

So since Oracle should be glad to have a customer use both E-Business Suite and SoaSuite, I would say that the E-Business Suite Adapter should be free. The EBS-Adapter should be a sales-argument to choose for SoaSuite in combination with EBS.

Honestly I can't explain why you should use the EBS adapter in such a setting. Because you can use the Database and AQ adapter just as good. The only real advantage of the EBS Adapter is that it will set the responsibility correctly. That is what you should add to the pl/sql wrapper functions that the Database Adapter generates. But that's no rocket sciense. In my experience the EBS-Adapter is a nice product, but I feel that the advantage is limited related to the license fee.

But maybe times change. On Open World the SOA Gateway for EBS is introduced. I got the following link from a colleague:
http://www.oracle.com/technology/products/applications/events/oow-2008/S298465_VeshaalSingh-SOA-EBS.pdf.
This explains the introduction of the SOA Gateway. And it seems that it is a special-packaging/deployment of the EBS Adapter right into the OC4J of the E-Business Suite. It adds a maintenance screen that enables you to choose a IRep interface and generate a WSDL for it. This Maintenance screen can be seen as a replacement of the adapter-wizard in JDeveloper.

It is possible to add custom API's to the IRep. It even has support for complex API's, both seeded and Third Party. These are in fact BPEL Processes that tigh the API's together. You can download them and as I understand it, you should deploy it to your separate BPEL PM.

At my current customer the ICT department asked for WSDL's on EBS. There aren't any (well a few, but I learned they're not of much use). But with the new SOA Gateway it should be easy to deliver them for every EBS-API there is.

Now let's hope it is delivered under the Foundation-license of EBS and that it is not an option...