Wednesday 18 February 2009

Original System References

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

Already in R11.5.10 you can create original system references for several TCA-tables. This means that for example for a party or site you can create a reference where you specify the reference-number which the object has in the a spoke system. This is part of the Customer Data Hub functionality.
For interfacing this is handy because you can track where the customer or relation is originated. And when you merge parties you can see in which other systems this party is also referenced.

In R12 you have to packages to create these references:
  • The original HZ_ORIG_SYSTEM_REF_PUB, which uses record-type variables.
  • The apparently new hz_orig_system_ref_bo_pub, which acts as a wrapper and accepts object-type variables.
The reference to the tables in TCA are done using an object-table-name together with the object-id/owner_table_id. These two attributes (object-id and object-type/table_name) specify the reference.

I had a problem with the object-type/table_name. Earlier I thought these were actually the same, but for example using an object-type like 'HZ_PARTIES', will give API-errors. So I thought I ran into a bug.

I couldn't find a patch for it. I did see some bugs relating to a Peoplesoft/EBusiness suite coupling using BPEL (maybe AIA-related) that hit this package, but apparently there is another problem. But it seems to me that without the line above it could not work.

Today I was introduced into the "Oracle eBusiness Suite Electronic Technical Reference Manual - eTRM" site. Since I'm mainly a tech-guy, I did not know of this site. Or actually, I figure that I've seen it earlier but it did not stick around in my short-term-memory.

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.

No comments :