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.
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
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.
AFAIK, the orig_system_reference fields cannot be updated from front end nor by an API.
If you must, raise an SR and see if Oracle can give a data fix script.
If not, then bite the bullet and do a direct table update. But be very careful and test thoroughly.
I don't know if it would be the same for customers as for suppliers, but the tables are the same. We were able to get the orig system reference updateable for suppliers in the GUI by doing the following:
Go to: System Administrator -> Profile -> System
Search for Profile Option Name: HZ: Source System Mapping Access
Set Site to: ‘Create and Update’
After this we could access this information and edit it through the 'Source Systems'-link.