This discussion is archived
3 Replies Latest reply: Jul 11, 2012 12:04 AM by camilla RSS

Updating orig_system_reference

user433263 Newbie
Currently Being Moderated
I have been tasked with updating orig_system_reference in Oracle EBS r12.06 as it is incorrect in many cases.

I have read up on the API's on metalkink and did go through the API code quite thouroughly.

I originally was using HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT to attempt to update orig_system_reference. I was using the sample code as a template and made sure all mandatory parameters were passed in.

I originally got the following error.

Updation of Customer Account failed:
1) The value for the orig_system+orig_system_reference column is not found in the HZ_ORIG_SYS_REFERENCES table.
2) You cannot update column orig_system_referen

It then seemed that I needed to create that record in HZ_ORIG_SYS_REFERENCES table. I was able to do this via the following call, again using the sample code provided and making sure I included all mandatory parameters.

APPS.hz_orig_system_ref_pub.create_orig_system_reference
(
p_init_msg_list => FND_API.G_TRUE,
p_orig_sys_reference_rec => p_orig_sys_reference_rec,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);

The HZ_ORIG_SYS_REFERENCES record was successfully created.

The API to update the customer also completed successfully but the orig_system_reference column was not updated so I traced through the API PL/SQL code and found the following.

I was able to bypass the non-updateable validation for orig_system_reference but then it just gets set to null anyway. So here the orig_system_reference gets set to null unless orig_system is null but it is impossible to create an orig_system as null, the API will fail with the following.

Calling the API hz_orig_system_ref_pub.create_orig_system_reference
Updation of Customer Account failed:Invalid value for orig_system. Please enter orig_system value from HZ_ORIG_SYSTEMS_B.
1) Invalid value for orig_system. Please enter orig_system value from HZ_ORIG_SYSTEMS_B.

if (p_cust_account_rec.orig_system is not null
and p_cust_account_rec.orig_system <>fnd_api.g_miss_char)
and (p_cust_account_rec.orig_system_reference is not null
and p_cust_account_rec.orig_system_reference <>fnd_api.g_miss_char)
then
p_cust_account_rec.orig_system_reference := null;
-- In mosr, we have bypassed osr nonupdateable validation
-- but we should not update existing osr, set it to null
end if;

Now we get to the UPDATE of hz_cust_accounts table. Since X_ORIG_SYSTEM_REFERENCE is now NULL we just update it to what it was already.

ORIG_SYSTEM_REFERENCE = DECODE( X_ORIG_SYSTEM_REFERENCE, NULL, ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR, TO_CHAR(X_CUST_ACCOUNT_ID), X_ORIG_SYSTEM_REFERENCE ),

My question is does anybody know if it is possible to update orig_system_reference via Oracle API's? If it is in the system incorrectly there should be a way to fix it I would think.

Thanks very much for anybody looking at this. I appreciate your time.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points