Thursday, August 29, 2013

How to update an existing value in HZ_CUST_ACCOUNTS table to NULL fnd_api.g_null_char

--How to assign null value to TCA APIs
--Take an example of HZ_CUST_ACCOUNTS.ATTRIBUTE1 column has an existing value of "YES"
--if you want to set this value to NULL by assigning NULL to the attribute1 record type and passing it to the TCA API  hz_cust_account_v2pub.update_cust_account
--will not set the value to null
-- We need to use "fnd_api.g_null_char" to set an existing value in the TCA table columns to NULL
-- Below example sets the HZ_CUST_ACCOUNTS.ATTRIBUTE1 which has an existing value of 'YES" to NULL
DECLARE
   x_return_status   VARCHAR2 (100);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (32767);
BEGIN
   lv_cust_account_rec.cust_account_id := 1234;
   l_acct_ovn := 5;
   lv_cust_account_rec.attribute1 := fnd_api.g_null_char;

   print_log ('Calling Customer Account Update API', 2);

   hz_cust_account_v2pub.update_cust_account (
      p_init_msg_list           => fnd_api.g_false,
      p_cust_account_rec        => lv_cust_account_rec,
      p_object_version_number   => l_acct_ovn,
      x_return_status           => x_return_status,
      x_msg_count               => x_msg_count,
      x_msg_data                => x_msg_data);

   IF x_return_status <> 'S'
   THEN
      FOR i IN 1 .. x_msg_count
      LOOP
         l_error_msg :=
            l_error_msg || i || '.'
            || SUBSTR (
                  NVL (fnd_msg_pub.get (p_encoded => fnd_api.g_false),
                       x_msg_data),
                  1,
                  500);
      END LOOP;
   END IF;

END;

No comments:

Post a Comment