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;

Useful SQL Queries to validate FND(Foundation)(Conc. Program, Req Set etc.,) and XDO(Xml Data Objects) Objects(RTF file, Template)

-- Select statement to identify which a concurrent_program

-- Parameters:

--        p_conc_prog - Concurrent Program Short Name

 

SELECT cp.user_concurrent_program_name, cp.concurrent_program_name

  FROM fnd_concurrent_programs_vl cp

 WHERE cp.concurrent_program_name = '&p_conc_prog';

 

-- Select statement to identify which request group is associated to a particular concurrent_program

-- Parameters:

--        p_conc_prog - Concurrent Program Name

--        p_req_group - Request Group Name

 

SELECT cp.user_concurrent_program_name,

       cp.concurrent_program_name,

       rg.request_group_name

  FROM fnd_request_group_units rgu,

       fnd_request_groups rg,

       fnd_concurrent_programs_vl cp

 WHERE     cp.concurrent_program_id = rgu.request_unit_id

       AND rg.request_group_id = rgu.request_group_id

       AND cp.concurrent_program_name = '&p_conc_prog'

       AND rg.request_group_name = '&p_req_group';

 

  -- Select statement to identify Request set and it's corresponding stages and stage links for a particular request set

-- Parameters:

--        p_req_set_short_name - Request Set Name

 

SELECT rs.request_set_name,

       rs.user_request_set_name,

       sv.stage_name,

       sv.request_set_stage_id,

       sv.success_link,

       sv.success_link_name,

       sv.warning_link,

       sv.warning_link_name,

       sv.error_link,

       sv.error_link_name,

       sv.display_sequence,

       sv.user_executable_name,

       sv.user_stage_name

  FROM apps.fnd_request_sets_vl rs, fnd_req_set_stages_form_v sv

 WHERE rs.request_set_name = UPPER('&p_req_set_short_name')

       AND sv.request_set_id = rs.request_set_id;

 

xx_ar_std_register_trans

 

-- Select statement to identify which request group is associated to a particular request set

-- Parameters:

--        p_req_set - Request Set Name

--        p_req_group - Request Group Name

 

SELECT cp.request_set_name, cp.user_request_set_name, rg.request_group_name

  FROM fnd_request_group_units rgu,

       fnd_request_groups rg,

       fnd_request_sets_vl cp

 WHERE     cp.request_set_id = rgu.request_unit_id

       AND rg.request_group_id = rgu.request_group_id

       AND cp.request_set_name = '&p_req_set'

       AND rg.request_group_name = '&p_req_group';

 

  --Select statement to validate RTF Template

  --Parameter

  --     p_template_code - Template Code

 

SELECT xl.lob_type,

       xl.application_short_name,

       xl.lob_code,

       xl.language,

       xl.file_name,

       xl.xdo_file_type,

       xl.file_content_type,

       xl.file_data,

       xl.program,

       xl.territory,

       xt.template_code,

       xt.data_source_code

  FROM xdo_lobs xl, xdo_templates_b xt

 WHERE xt.template_code = xl.lob_code(+)

       AND template_code = '&p_template_code';

 

--Select statement to validate Forms Personalization

-- Parameter

--    p_func_name - Form Function Name

 

SELECT ff.user_function_name,

       ff.user_function_name,

       ff.function_id,

       cr.description,

       cr.trigger_event,

       cr.trigger_object,

       cr.condition,

       cr.sequence,

       cr.form_name

  FROM fnd_form_custom_rules cr, fnd_form_functions_vl ff

 WHERE ff.function_name = cr.function_name

       AND ff.function_name = '&p_func_name';

 

 -- Select statement to validate Custom Profile

-- Parameter:

--       p_prof_name -- Profile Option Name

 

SELECT po.profile_option_name,

       po.user_profile_option_name,

       pov.profile_option_value

  FROM fnd_profile_options_vl po, fnd_profile_option_values pov

 WHERE pov.profile_option_id = po.profile_option_id

       AND po.profile_option_name LIKE UPPER ('&p_prof_name%');

 

-- Select statement to identify association of Form function to a particular Menu as a menu entry

-- Parameters:

--        p_menu_name - Menu Name

--        p_function_name - Function Name

 

SELECT mv.menu_name,

       ff.function_name,

       ff.user_function_name,

       me.prompt,

       me.description Entry_Description

  FROM fnd_menus_vl mv, fnd_menu_entries_vl me, fnd_form_functions_vl ff

 WHERE     mv.menu_id = me.menu_id

       AND mv.menu_name = '&p_menu_name'

       AND ff.function_id = me.function_id

       AND ff.function_name = '&p_function_name';