Updating a contacts email and phone in Oracles HZ_CONTACT_POINTS through PL/SQL

Question

Is it OK to update the EMAIL_ADDRESS or PHONE_NUMBER columns from a PL/SQL procedure for a contact that resides in HZ_CONTACT_POINTS manually using an update query similar to below?

UPDATE hz_contact_points SET phone_number = P_PHONE_NUMBER WHERE owner_table_id = p_party_id AND owner_table_name = 'HZ_PARTIES' and primary_flag = 'Y' AND status = 'A' AND UPPER(contact_point_type) = 'PHONE';

OR

UPDATE hz_contact_points SET email_address = P_EMAIL_ADDRESS WHERE owner_table_id = p_party_id AND owner_table_name = 'HZ_PARTIES' AND PRIMARY_FLAG = 'Y' AND STATUS = 'A' AND UPPER(CONTACT_POINT_TYPE) = 'EMAIL';

Or does one have to use the API calls:

HZ_CONTACT_POINT_V2PUB.update_phone_contact_point

OR

HZ_CONTACT_POINT_V2PUB.update_email_contact_point

Answer 1

HZ_CONTACT_POINT_V2PUB is part of the interface for TCA. To quote from the TCA documentation:

"Oracle Trading Community Architecture (TCA) is a data model that allows you to manage complex information about the parties, or customers, who belong to your commercial community, including organizations, locations, and the network of hierarchical relationships among them."

In particular, that complex information includes a version history. That's what the Oracle Apps APIs do: execute the actual DML and also orchestrates the concomitant changes (dependencies, versioning, audit, etc) to ensure the consistency of that data model.

If you use SQL and update an attribute manually you won't do any of that.

So, is that "okay"? Maybe you'll get away with it and not break anything. Or perhaps your organization doesn't care about the version history. But they have spent a lot of money licensing Oracle Apps. So my advice would be to use the API, even it it seems like overkill for such a trivial action. Because if you make a habit of bypassing the API eventually you will break the data model, and then Oracle Support will ask some very hard questions.