Oracle PLSQL string tokenizer package procedure to break a string into tokens based on a delimiter

--Following package procedure is used to break a string into substrings or tokens based on a delimiter

CREATE PACKAGE BODY xx_token_pkg
 AS   TYPE token_tbl IS TABLE OF VARCHAR2 (2000)
INDEX BY BINARY_INTEGER;

PROCEDURE string_tokenizer (
                                      p_string        IN        VARCHAR2,
                                      p_delimiter    IN        VARCHAR2,
                                      p_result_tbl   OUT     xx_ar_billing_ext_pkg.token_tbl
                                           )
   IS

      CURSOR c_tokens
      IS
         SELECT     LEVEL,
                    SUBSTR (string_to_tokenize,
                            DECODE (LEVEL,
                                    1, 1,
                                      INSTR (string_to_tokenize,
                                             delimiter,
                                             1,
                                             LEVEL - 1
                                            )
                                    + 1
                                   ),
                              INSTR (string_to_tokenize, delimiter, 1, LEVEL)
                            - DECODE (LEVEL,
                                      1, 1,
                                        INSTR (string_to_tokenize,
                                               delimiter,
                                               1,
                                               LEVEL - 1
                                              )
                                      + 1
                                     )
                           ) token
               FROM (SELECT p_string || p_delimiter AS string_to_tokenize,
                            p_delimiter AS delimiter
                       FROM DUAL)
         CONNECT BY INSTR (string_to_tokenize, delimiter, 1, LEVEL) > 0
         ORDER BY LEVEL ASC;

      l_index   BINARY_INTEGER := 0;

   BEGIN

      FOR token_rec IN c_tokens

      LOOP
         p_result_tbl (l_index) := token_rec.token;
         l_index := l_index + 1;
      END LOOP;

   EXCEPTION

     WHEN OTHERS THEN
       print_log('Exception in string_tokenizer'||sqlerrm);
   END string_tokenizer;

END xx_token_pkg;

--Following is an anonymous block which calls xx_token_pkg.string_tokenizer to break the string into substrings/tokens using a separater/delimiter '-'DECLARE

   l_result   xx_token_pkg.token_tbl;

BEGIN

   xx_token_pkg.string_tokenizer ('Test1-Test2-Test3', '-', l_result);

   FOR i IN l_result.FIRST .. l_result.LAST

   LOOP

      DBMS_OUTPUT.put_line ('result=' || l_result (i));

   END LOOP;

END;

Running the above blcok results in the following output:-

result=Test1
result=Test2
result=Test3

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;

TCA API Example to Update Party Site using hz_party_site_v2pub.update_party_site

DECLARE

   l_party_site_rec   hz_party_site_v2pub.PARTY_SITE_REC_TYPE;

   l_obj_num          NUMBER := 2;

   l_return_status    VARCHAR2 (1);

   l_msg_count        NUMBER;

   l_msg_data         VARCHAR2 (2000);

BEGIN

   l_party_site_rec.party_site_id := &p_party_site_id;

   l_party_site_rec.status := '&p_status';

   l_party_site_rec.identifying_address_flag := 'p_id_address';

   l_obj_num := &p_ovn;

   hz_party_site_v2pub.update_party_site (

      p_init_msg_list           => FND_API.G_FALSE,

      p_party_site_rec          => l_party_site_rec,

      p_object_version_number   => l_obj_num,

      x_return_status           => l_return_status,

      x_msg_count               => l_msg_count,

      x_msg_data                => l_msg_data);

   DBMS_OUTPUT.put_line ('Ret Status:' || l_return_status);

   DBMS_OUTPUT.put_line ('l_msg_data:' || l_msg_data);

 

   IF l_msg_count > 0

   THEN

      FOR I IN 1 .. l_msg_count

      LOOP

         DBMS_OUTPUT.put_line (

               I

            || '.'

            || SUBSTR (FND_MSG_PUB.Get (p_encoded => FND_API.G_FALSE), 1, 255));

      END LOOP;

   END IF;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Error:' || SQLERRM);

END;