3 Replies Latest reply: Jul 11, 2012 2:04 AM by camilla RSS

    Updating orig_system_reference

    user433263
      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.