Script for testing TCA Api's

If you try to test a service generated and deployed using the Service Provider in SOA Gateway, you might run into problems while the response message does not provide the appropriate messages on the functional errors.
You passed the security authentication and the apps-authorisation and the in the SOA Monitor the webservice shows a succesful execution. But there might be fnctional errors like a lookup violation that are not shown in the response message.

Then it might be usefull to just call the corresponding pl/sql api from a test script.

Below you'll find a script to test the Create Person of the Public Party API. It's a Pl/Sql Developer test script that you might adapt to an sql plus or sqldeveloper script.

declare
  -- FND UserName
  c_user_name          constant varchar2(30) := 'ASADMIN';
  c_responsibility_key constant varchar2(30) := 'HZ_TCA_MANAGER';
  -- Type for repsonsibility record
  type t_responsibility_rec is record(
    user_id            fnd_user.user_id%type,
    user_name          fnd_user.user_name%type,
    responsibility_key fnd_responsibility.responsibility_key%type,
    responsibility_id  fnd_responsibility.responsibility_id%type,
    appplication_id    fnd_responsibility.application_id%type);
  g_responsibility xxx_profile.t_responsibility_rec;
  -- Person Record
  p_person_rec hz_party_v2pub.person_rec_type;
  -- Error Message Fields
  l_error_text varchar2(32767);
  l_msg_count  number;
  cursor c_usr(b_user_name in fnd_user.user_name%type) is
    select usr.user_id, usr.user_name
      from fnd_user usr
     where usr.user_name = b_user_name;
  r_usr c_usr%rowtype;
  cursor c_rsp(b_responsibility_key in fnd_responsibility.responsibility_key%type) is
    select rsp.application_id,
           rsp.responsibility_id,
           rsp.responsibility_key
      from fnd_responsibility rsp
     where rsp.responsibility_key = b_responsibility_key;
  r_rsp            c_rsp%rowtype;
begin
  -- Query Responsibility Id's
  open c_usr(b_user_name => c_user_name);
  fetch c_usr
    into r_usr;
  if c_usr%found then
    g_responsibility.user_id   := r_usr.user_id;
    g_responsibility.user_name := r_usr.user_name;
    open c_rsp(b_responsibility_key => c_responsibility_key);
    fetch c_rsp
      into r_rsp;
    if c_rsp%found then
      g_responsibility.responsibility_key := r_rsp.responsibility_key;
      g_responsibility.responsibility_id  := r_rsp.responsibility_id;
      g_responsibility.appplication_id    := r_rsp.application_id;
    end if;
    close c_rsp;
  end if;
  close c_usr;
  -- Set Apps context
  fnd_global.apps_initialize(user_id      => g_responsibility.user_id,
                             resp_id      => g_responsibility.responsibility_id,
                             resp_appl_id => g_responsibility.appplication_id);
  -- Set Person Record
  p_person_rec.PERSON_FIRST_NAME          := 'Jean';
  p_person_rec.PERSON_MIDDLE_NAME         := 'Michel';
  p_person_rec.PERSON_LAST_NAME           := 'Jarre';
  p_person_rec.PERSON_INITIALS            := 'JM';
  p_person_rec.PERSON_NAME_PHONETIC       := 'sjan misjel sjar';
  p_person_rec.PERSON_FIRST_NAME_PHONETIC := 'sjan';
  p_person_rec.PERSON_LAST_NAME_PHONETIC  := 'sjar';
  p_person_rec.MIDDLE_NAME_PHONETIC       := 'misjel';
  p_person_rec.DATE_OF_BIRTH              := to_date('1948-08-26',
                                                     'yyyy-mm-dd');
  p_person_rec.PLACE_OF_BIRTH             := 'Lyon';
  p_person_rec.GENDER                     := 'MALE';
  p_person_rec.DECLARED_ETHNICITY         := 'French';
  p_person_rec.created_by_module          := 'HZ_WS';
  -- Call the procedure
  hz_party_v2pub.create_person(p_init_msg_list    => :p_init_msg_list,
                               p_person_rec       => p_person_rec,
                               p_party_usage_code => :p_party_usage_code,
                               x_party_id         => :x_party_id,
                               x_party_number     => :x_party_number,
                               x_profile_id       => :x_profile_id,
                               x_return_status    => :x_return_status,
                               x_msg_count        => :x_msg_count,
                               x_msg_data         => :x_msg_data);
  l_msg_count  := fnd_msg_pub.Count_Msg;
  l_error_text := '';
  if l_msg_count = 1 then
    l_error_text := 'API Error: ';
  end if;
  if l_msg_count >= 1 then
    for i in 1 .. l_msg_count loop
      l_error_text := nvl(l_error_text, ' ') || chr(10) || i || '. ' ||
                      fnd_msg_pub.get(p_encoded => fnd_api.g_false);
    end loop;
  
  end if;
  :error := l_error_text;
end;