Wednesday 13 April 2011

SOA Gateway Service Invocation Framework: Test Service

This week I'm delivering a workshop on SOA Gateway of E-Business Suite 12.1.
On implementing the Service Invocation Framework there is a good blog-article here.
I found myself running into an error using the "raise in java" button from the test screen. And I was pretty sure that my values were ok.
The error was:
Exception occured in Java Raise - Exception when Invoking Web Service -> oracle.apps.fnd.wf.bes.InvokerException: HTTP transport error: 
javax.xml.soap.SOAPException: java.security.PrivilegedActionException: 
javax.xml.soap.SOAPException: Message send failed: For input string: ""
Since I have a background in Pl/Sql and using the Oracle Workflow Business Event system in Pl/Sql, I got some of my BES-example code from the shelf and build a package to invoke the event from Pl/Sql.

Here is the package specification of my example code:

create or replace package xxx_soa_events is
  /*******************************************************************************************
    Package with functionality to support SOA Gateway Service Invocation Framework Events
  
    #Version  0.1
    #Author   Martien van den Akker, Darwin-IT Professionals
    #Usage    Methods to get system_parameters
  
    <b>Change History</b>
  
    When Who: What
    <ul>
     <li>07-apr-2011 Martien van den Akker: Initial Creation</li>
    </ul>
  ********************************************************************************************/

  /* Name create person event */
  c_evt_create_person constant varchar2(100) := 'darwin.apps.soa.create_person';
  /* Event Rule result Error */
  c_rst_error constant varchar2(10) := 'ERROR';
  /* Event Rule result Success */
  c_rst_success constant varchar2(10) := 'SUCCESS';
  /* Event Rule result Warning */
  c_rst_warning constant varchar2(10) := 'WARNING';
  /* HZ Namespace declaration*/
  c_hz_ns_decl   constant varchar2(150) := 'xmlns:hz="http://xmlns.oracle.com/apps/hz/soaprovider/plsql/hz_party_v2pub/"';
  c_hz_ns_abbrev constant varchar2(10) := 'hz';
  c_tag_soahdr   constant varchar2(20) := 'SOAHeader';
  c_tag_Resp     constant varchar2(20) := 'Responsibility';
  c_tag_Respapp  constant varchar2(20) := 'RespApplication';
  c_tag_secgrp   constant varchar2(20) := 'SecurityGroup';
  c_tag_nlslang  constant varchar2(20) := 'NLSLanguage';
  c_tag_orgid    constant varchar2(20) := 'Org_Id';
  c_val_Resp     constant varchar2(20) := 'HZ_TCA_MANAGER';
  c_val_Respapp  constant varchar2(20) := 'AR';
  c_val_secgrp   constant varchar2(20) := 'STANDARD';
  c_val_nlslang  constant varchar2(20) := 'AMERICAN';
  c_val_orgid    constant varchar2(20) := '';
  c_par_inp_hdr  constant varchar2(20) := 'WFBES_INPUT_HEADER';

  c_hz_crepers_ns_decl   constant varchar2(150) := 'xmlns:cre="http://xmlns.oracle.com/apps/hz/soaprovider/plsql/hz_party_v2pub/create_person/"';
  c_hz_crepers_ns_abbrev constant varchar2(10) := 'cre';
  c_tag_inppar           constant varchar2(20) := 'InputParameters';

  /*******************************************************************************************
    Default Rule
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Default Rule function as an example implementation for a pl/sql subscription
  
    #param    p_subscription_guid    Guid of the subscription of which this rule function is 
                                     the implementation
    #param    p_event                event object
    #return   status code
  ********************************************************************************************/
  function default_rule(p_subscription_guid in raw,
                        p_event             in out wf_event_t)
    return varchar2;
  /*******************************************************************************************
    Default Rule
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Rule function to save events as a response in Service Invocation Framework
  
    #param    p_subscription_guid    Guid of the subscription of which this rule function is 
                                     the implementation
    #param    p_event                event object
    #return   status code
  ********************************************************************************************/
  function save_response_events(p_subscription_guid in raw, p_event in out wf_event_t)

    return varchar2;
  /*******************************************************************************************
    Create a xml element
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Raise Event to Create a Person via SIF
    
    #param    p_ns_abbrev               optional namespace abbreviation
    #param    p_tag_name                name of the tag
    #param    p_value                   element value
    #param    p_attrib                  optional attribute/value pair
    #return   element
  ********************************************************************************************/
  function create_element(p_ns_abbrev in varchar2,
                          p_tag_name  in varchar2,
                          p_value     in varchar2,
                          p_attrib    in varchar2) return varchar2;
  /*******************************************************************************************
    Create a hz SOA Header
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage   Create the HZ SOA Header denoting the responsibility
  
    #return   element
  ********************************************************************************************/
  function create_hz_soaheader return varchar2;
  /*******************************************************************************************
    Create a create person data object
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage   Create the HZ SOA Header denoting the responsibility
    
    #param p_person_first_name           in varchar2
    #param p_person_middle_name          in varchar2
    #param p_person_last_name            in varchar2
    #param p_person_initials             in varchar2
    #param p_person_name_phonetic        in varchar2
    #param p_person_first_name_phonetic  in varchar2
    #param p_person_last_name_phonetic   in varchar2
    #param p_middle_name_phonetic        in varchar2
    #param p_date_of_birth               in varchar2
    #param p_place_of_birth              in varchar2
    #param p_gender                      in varchar2
    #param p_declared_ethnicity          in varchar2
    #param p_created_by_module           in varchar2
    #return   element
  ********************************************************************************************/
  function create_hz_createperson_data(p_person_first_name          in varchar2,
                                       p_person_middle_name         in varchar2,
                                       p_person_last_name           in varchar2,
                                       p_person_initials            in varchar2,
                                       p_person_name_phonetic       in varchar2,
                                       p_person_first_name_phonetic in varchar2,
                                       p_person_last_name_phonetic  in varchar2,
                                       p_middle_name_phonetic       in varchar2,
                                       p_date_of_birth              in varchar2,
                                       p_place_of_birth             in varchar2,
                                       p_gender                     in varchar2,
                                       p_declared_ethnicity         in varchar2,
                                       p_created_by_module          in varchar2)
    return clob;
  /*******************************************************************************************
    Raise Create Person Event
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Raise Event to Create a Person via SIF
  
    #param p_event_key                   event key
    #param p_person_first_name           in varchar2
    #param p_person_middle_name          in varchar2
    #param p_person_last_name            in varchar2
    #param p_person_initials             in varchar2
    #param p_person_name_phonetic        in varchar2
    #param p_person_first_name_phonetic  in varchar2
    #param p_person_last_name_phonetic   in varchar2
    #param p_middle_name_phonetic        in varchar2
    #param p_date_of_birth               in varchar2
    #param p_place_of_birth              in varchar2
    #param p_gender                      in varchar2
    #param p_declared_ethnicity          in varchar2
    #param p_created_by_module           in varchar2
  ********************************************************************************************/
  procedure raise_create_person_event(p_event_key                  in varchar2,
                                      p_person_first_name          in varchar2,
                                      p_person_middle_name         in varchar2,
                                      p_person_last_name           in varchar2,
                                      p_person_initials            in varchar2,
                                      p_person_name_phonetic       in varchar2,
                                      p_person_first_name_phonetic in varchar2,
                                      p_person_last_name_phonetic  in varchar2,
                                      p_middle_name_phonetic       in varchar2,
                                      p_date_of_birth              in varchar2,
                                      p_place_of_birth             in varchar2,
                                      p_gender                     in varchar2,
                                      p_declared_ethnicity         in varchar2,
                                      p_created_by_module          in varchar2);
end xxx_soa_events;

And below the body of the example package:
create or replace package body xxx_soa_events is
  c_package_name constant varchar2(30) := 'xxx_soa_events';
  /*******************************************************************************************
    Create an xml open tag
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    String XML Handling
  
    #param    p_tag_name                name of the tag
    #param    p_attrib                  optional attribute/value pair
    #return   tag
  ********************************************************************************************/
  function open_tag(p_tag_name in varchar2, p_attrib in varchar2)
    return varchar2 is
    l_tag varchar2(2000);
  begin
    if p_attrib is null then
      l_tag := '<' || p_tag_name || '>';
    else
      l_tag := '<' || p_tag_name || ' ' || p_attrib || ' >';
    end if;
    return l_tag;
  end;
  /*******************************************************************************************
    Create a xml close tag
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    String XML Handling
  
    #param    p_tag_name                name of the tag
    #return   tag
  ********************************************************************************************/
  function close_tag(p_tag_name in varchar2) return varchar2 is
    l_tag varchar2(2000);
  begin
    l_tag := '</' || p_tag_name || '>';
    return l_tag;
  end;
  /*******************************************************************************************
    Create a xml element
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    String XML Handling
    
    #param    p_ns_abbrev               optional namespace abbreviation
    #param    p_tag_name                name of the tag
    #param    p_value                   element value
    #param    p_attrib                  optional attribute/value pair
    #return   element
  ********************************************************************************************/
  function create_element(p_ns_abbrev in varchar2,
                          p_tag_name  in varchar2,
                          p_value     in varchar2,
                          p_attrib    in varchar2) return varchar2 is
    l_return varchar2(32767);
    l_tag    varchar2(2000);
  begin
    if p_ns_abbrev is not null then
      l_tag := p_ns_abbrev || ':' || p_tag_name;
    else
      l_tag := p_tag_name;
    end if;
    l_return := open_tag(l_tag, p_attrib) || p_value || close_tag(l_tag);
    return l_return;
  end;
  /*******************************************************************************************
    Create a xml element
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Create an XML element
    
    #param    p_ns_abbrev               optional namespace abbreviation
    #param    p_tag_name                name of the tag
    #param    p_value                   element value
    #return   element
  ********************************************************************************************/
  function create_element(p_ns_abbrev in varchar2,
                          p_tag_name  in varchar2,
                          p_value     in varchar2) return varchar2 is
    l_return varchar2(32767);
  begin
    l_return := create_element(p_ns_abbrev => p_ns_abbrev,
                               p_tag_name  => p_tag_name,
                               p_attrib    => null,
                               p_value     => p_value);
    return l_return;
  end;
  /*******************************************************************************************
    Create a hz SOA Header
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage   Create the HZ SOA Header denoting the responsibility
  
    #return   element
  ********************************************************************************************/
  function create_hz_soaheader return varchar2 is
    l_return varchar2(2000);
  begin
    l_return := create_element(p_ns_abbrev => c_hz_ns_abbrev,
                               p_tag_name  => c_tag_soahdr,
                               p_attrib    => c_hz_ns_decl,
                               p_value     => create_element(p_ns_abbrev => c_hz_ns_abbrev,
                                                             p_tag_name  => c_tag_Resp,
                                                             p_value     => c_val_Resp) ||
                                              create_element(p_ns_abbrev => c_hz_ns_abbrev,
                                                             p_tag_name  => c_tag_Respapp,
                                                             p_value     => c_val_Respapp) ||
                                              create_element(p_ns_abbrev => c_hz_ns_abbrev,
                                                             p_tag_name  => c_tag_secgrp,
                                                             p_value     => c_val_secgrp) ||
                                              create_element(p_ns_abbrev => c_hz_ns_abbrev,
                                                             p_tag_name  => c_tag_nlslang,
                                                             p_value     => c_val_nlslang) ||
                                              create_element(p_ns_abbrev => c_hz_ns_abbrev,
                                                             p_tag_name  => c_tag_orgid,
                                                             p_value     => c_val_orgid));
  
    return l_return;
  end;
  /*******************************************************************************************
    Varchar2 To Clob
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Convert a clob to a varchar2
  
    #param    p_varchar2         varchar2
    #return   clob
  ********************************************************************************************/
  function varchar_to_clob(p_varchar2 varchar2) return clob is
    l_clob clob;
  begin
    -- Open clob
    -- create the temporary CLOB's to store the xml data that is sent to and received from the xml open gateway
    dbms_lob.createtemporary(l_clob, false, dbms_lob.session);
    dbms_lob.open(l_clob, dbms_lob.lob_readwrite);
    -- Convert the input to clob
    dbms_lob.writeappend(l_clob, length(p_varchar2), p_varchar2);
    -- Close the Clobs
    dbms_lob.close(l_clob);
    -- Return
    return l_clob;
  end varchar_to_clob;
  /*******************************************************************************************
    Clob To Varchar2
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Convert a clob to a varchar2
  
    #param    p_clob         clob
    #return   varchar2
  ********************************************************************************************/
  function clob_to_varchar2(p_clob in clob) return varchar2 is
    l_length  number;
    l_message varchar2(32767);
  begin
    l_length := dbms_lob.getlength(p_clob);
    l_length := greatest(l_length, 32767);
    if l_length > 32767 then
      l_length := 32767;
    end if;
    l_message := dbms_lob.substr(p_clob, l_length, 1);
    return l_message;
  exception
    when others then
      raise;
  end clob_to_varchar2;

  /*******************************************************************************************
    Create a hz SOA Header
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage   Create the HZ SOA Header denoting the responsibility
  
    #return   element
  ********************************************************************************************/
  function create_hz_crepers_inp_elt(p_value varchar2) return varchar2 is
    l_person_elt varchar2(32767);
  begin
    l_person_elt := create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                   p_tag_name  => c_tag_inppar,
                                   p_attrib    => c_hz_crepers_ns_decl,
                                   p_value     => p_value);
  
    return l_person_elt;
  end;

  /*******************************************************************************************
    Create a create person data object
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage   Create the HZ SOA Header denoting the responsibility
    
    #param p_person_first_name           in varchar2
    #param p_person_middle_name          in varchar2
    #param p_person_last_name            in varchar2
    #param p_person_initials             in varchar2
    #param p_person_name_phonetic        in varchar2
    #param p_person_first_name_phonetic  in varchar2
    #param p_person_last_name_phonetic   in varchar2
    #param p_middle_name_phonetic        in varchar2
    #param p_date_of_birth               in varchar2
    #param p_place_of_birth              in varchar2
    #param p_gender                      in varchar2
    #param p_declared_ethnicity          in varchar2
    #param p_created_by_module           in varchar2
    #return   element
  ********************************************************************************************/
  function create_hz_createperson_data(p_person_first_name          in varchar2,
                                       p_person_middle_name         in varchar2,
                                       p_person_last_name           in varchar2,
                                       p_person_initials            in varchar2,
                                       p_person_name_phonetic       in varchar2,
                                       p_person_first_name_phonetic in varchar2,
                                       p_person_last_name_phonetic  in varchar2,
                                       p_middle_name_phonetic       in varchar2,
                                       p_date_of_birth              in varchar2,
                                       p_place_of_birth             in varchar2,
                                       p_gender                     in varchar2,
                                       p_declared_ethnicity         in varchar2,
                                       p_created_by_module          in varchar2)
    return clob is
    l_input_pars    varchar2(32767);
    l_person_data   varchar2(32767);
    l_person_rec    varchar2(32767);
    l_init_msg_list varchar2(200);
    l_clob          clob;
  
  begin
    l_init_msg_list := create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'P_INIT_MSG_LIST',
                                      p_value     => 'T');
    l_person_rec    := create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'PERSON_FIRST_NAME',
                                      p_value     => p_person_first_name) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'PERSON_MIDDLE_NAME',
                                      p_value     => p_person_middle_name) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'PERSON_LAST_NAME',
                                      p_value     => p_person_last_name) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'PERSON_INITIALS',
                                      p_value     => p_person_initials) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'PERSON_NAME_PHONETIC',
                                      p_value     => p_person_name_phonetic) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'PERSON_FIRST_NAME_PHONETIC',
                                      p_value     => p_person_first_name_phonetic) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'PERSON_LAST_NAME_PHONETIC',
                                      p_value     => p_person_last_name_phonetic) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'MIDDLE_NAME_PHONETIC',
                                      p_value     => p_middle_name_phonetic) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'DATE_OF_BIRTH',
                                      p_value     => p_date_of_birth) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'PLACE_OF_BIRTH',
                                      p_value     => p_place_of_birth) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'GENDER',
                                      p_value     => p_gender) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'DECLARED_ETHNICITY',
                                      p_value     => p_declared_ethnicity) ||
                       create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                      p_tag_name  => 'CREATED_BY_MODULE',
                                      p_value     => p_created_by_module);
  
    l_person_data := create_element(p_ns_abbrev => c_hz_crepers_ns_abbrev,
                                    p_tag_name  => 'P_PERSON_REC',
                                    p_value     => l_person_rec);
    l_input_pars  := create_hz_crepers_inp_elt(l_init_msg_list ||
                                               l_person_data);
    l_clob        := varchar_to_clob(l_input_pars);
    return l_clob;
  end;
  /*******************************************************************************************
    Raise Create Person Event
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Raise Event to Create a Person via SIF
  
    #param p_event_key                   event key
    #param p_person_first_name           in varchar2
    #param p_person_middle_name          in varchar2
    #param p_person_last_name            in varchar2
    #param p_person_initials             in varchar2
    #param p_person_name_phonetic        in varchar2
    #param p_person_first_name_phonetic  in varchar2
    #param p_person_last_name_phonetic   in varchar2
    #param p_middle_name_phonetic        in varchar2
    #param p_date_of_birth               in varchar2
    #param p_place_of_birth              in varchar2
    #param p_gender                      in varchar2
    #param p_declared_ethnicity          in varchar2
    #param p_created_by_module           in varchar2
  ********************************************************************************************/
  procedure raise_create_person_event(p_event_key                  in varchar2,
                                      p_person_first_name          in varchar2,
                                      p_person_middle_name         in varchar2,
                                      p_person_last_name           in varchar2,
                                      p_person_initials            in varchar2,
                                      p_person_name_phonetic       in varchar2,
                                      p_person_first_name_phonetic in varchar2,
                                      p_person_last_name_phonetic  in varchar2,
                                      p_middle_name_phonetic       in varchar2,
                                      p_date_of_birth              in varchar2,
                                      p_place_of_birth             in varchar2,
                                      p_gender                     in varchar2,
                                      p_declared_ethnicity         in varchar2,
                                      p_created_by_module          in varchar2) is
    l_data             clob;
    l_parameters       wf_parameter_list_t := wf_parameter_list_t();
    l_header_parameter wf_parameter_t;
  begin
    l_header_parameter := wf_parameter_t(name  => c_par_inp_hdr,
                                         value => create_hz_soaheader);
    l_data             := create_hz_createperson_data(p_person_first_name          => p_person_first_name,
                                                      p_person_middle_name         => p_person_middle_name,
                                                      p_person_last_name           => p_person_last_name,
                                                      p_person_initials            => p_person_initials,
                                                      p_person_name_phonetic       => p_person_name_phonetic,
                                                      p_person_first_name_phonetic => p_person_first_name_phonetic,
                                                      p_person_last_name_phonetic  => p_person_last_name_phonetic,
                                                      p_middle_name_phonetic       => p_middle_name_phonetic,
                                                      p_date_of_birth              => p_date_of_birth,
                                                      p_place_of_birth             => p_place_of_birth,
                                                      p_gender                     => p_gender,
                                                      p_declared_ethnicity         => p_declared_ethnicity,
                                                      p_created_by_module          => p_created_by_module);
    l_parameters.extend;
    l_parameters(l_parameters.count) := l_header_parameter;
    wf_event.raise(p_event_name => c_evt_create_person,
                   p_event_key  => p_event_key,
                   p_event_data => l_data,
                   p_parameters => l_parameters);
  end;
  /*******************************************************************************************
    Default Rule
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Default Rule function as an example implementation for a pl/sql subscription
  
    #param    p_subscription_guid    Guid of the subscription of which this rule function is 
                                     the implementation
    #param    p_event                event object
    #return   status code
  ********************************************************************************************/
  function default_rule(p_subscription_guid in raw,
                        p_event             in out wf_event_t)
    return varchar2 is
    c_module_name constant varchar2(30) := 'default_rule';
   -- l_event_name varchar2(240);
   -- l_event_key  varchar2(240);
  begin
    /* l_event_name := p_event.getEventName;
    l_event_key  := p_event.getEventKey;
     xxx_logging.log(p_message =>  c_module_name
                              || '.'|| c_package_name
                              || ': '|| l_event_name
                              || ', '|| l_event_key
                   , p_log_level => xxx_logging.c_debug_level);
    if  p_event.parameter_list.count > 0
    then
      for l_idx in p_event.parameter_list.first..p_event.parameter_list.last
      loop
        xxx_tca_api.log( p_module_name  => c_module_name
                       , p_package_name => c_package_name
                       , p_text         => l_event_name
                       , p_parameter1   => p_event.parameter_list(l_idx).getname
                                        ||': '||p_event.parameter_list(l_idx).getvalue);
      end loop;
    end if;*/
    /*  <optional code for WARNING>
    wf_core.context( pkg_name  => c_package_name
                   , proc_name => c_module_name
                   , arg1      => p_event.geteventname( )
                   , arg2      =>  p_subscription_guid);
    wf_event.seterrorinfo( p_event => p_event
                         , p_type  => c_rst_warning);
    return c_rst_warning;*/
    return c_rst_success;
  exception
    when others then
      wf_core.context(pkg_name  => c_package_name,
                      proc_name => c_module_name,
                      arg1      => p_event.geteventname,
                      arg2      => p_subscription_guid,
                      arg3      => p_event.getEventKey);
      wf_event.seterrorinfo(p_event => p_event, p_type => c_rst_error);
      return c_rst_error;
  end default_rule;
  /*******************************************************************************************
    Default Rule
  
    #Version  0.1
    #Author   Martien van den Akker
    #usage    Rule function to save events as a response in Service Invocation Framework
  
    #param    p_subscription_guid    Guid of the subscription of which this rule function is 
                                     the implementation
    #param    p_event                event object
    #return   status code
  ********************************************************************************************/
  function save_response_events(p_subscription_guid in raw, p_event in out wf_event_t)
    return varchar2 is
    c_module_name constant varchar2(30) := 'default_rule';
    l_event_name varchar2(240);
    l_event_key  varchar2(240);
  begin
    l_event_name := p_event.getEventName;
    l_event_key  := p_event.getEventKey;
    insert into xxx_soa_response_events
      (event_key, event_name, event)
    values
      (l_event_key, l_event_name, p_event);
    return c_rst_success;
  exception
    when others then
      wf_core.context(pkg_name  => c_package_name,
                      proc_name => c_module_name,
                      arg1      => p_event.geteventname,
                      arg2      => p_subscription_guid,
                      arg3      => p_event.getEventKey);
      wf_event.seterrorinfo(p_event => p_event, p_type => c_rst_error);
      return c_rst_error;
  end save_response_events;
begin
  -- Initialization
  Null;
end xxx_soa_events;
The main method here is raise_create_person_event. It get's the parameters that are needed for the call to the Create Person API. It uses some varchar2-based functions to build up an xml request message and the Apps Soa-Header.
With this method I managed to raise an event that called the service.


The methods default_rule and save_response_events show examples of a pl/sql rulefunction that you can use to subscribe on a business event.

Tuesday 12 April 2011

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;

SOA Gateway wsse security username token

When you want to use a service generated with the EBS 12.1 SOA Gateway you'll need a wsse:security block in the soap header to authenticated against the Username token. That is when you choose to use Username-token as authentication method.
The block that you need to add to the soap header looks like:

<wsse:Security>
  <wsse:UsernameToken> 
    <wsse:Username>ASADMIN</wsse:Username>
    <wsse:Password Type="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-username-token-profile-1.0#PasswordText">AsAdminPassword</wsse:Password>
  </wsse:UsernameToken> 
</wsse:Security>

The namespace that the abbreviation wsse: points to is:
xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd"