SAP JCO Connectivity Test

This will be a small post on testing SAP JCO connection. Usually for connecting to SAP systems from Oracle SOA suite, we make use of the SAP adapter and create a SAP target from Application Explorer(AE). After entering the basic credentials like username,password, clientid, system number and application server we can try connecting to SAP system from AE itself. This internally calls the SAP JCO classes to establish the connection.

We can also directly test the connection outside of AE by using the SAP JCO classes. Below sample code can be used for testing the SAP JCO connectivity.

package JCOConnectionTest;
import com.sap.mw.jco.*;
public class JCOConnector {
  public JCOConnector() {
    super();
  }
  JCO.Client mConnection;
  public void Connect1() {
  try {
  // Change the logon information to your own system/user
          mConnection =
             JCO.createClient("010", // SAP client
               "SAP_USER", // userid
               "sap_pswd", // password
               null, // language
               "sap_server", // application server host name
               "00"); // system number
          mConnection.connect();
          System.out.println(mConnection.getAttributes());
          mConnection.disconnect();
       }
       catch (Exception ex) {
         ex.printStackTrace();
         System.exit(1);
       }
  }
      public static void main (String args[]) {
       JCOConnector app = new JCOConnector();
       app.Connect1();
      }
}  

Make sure the sapjco.jar file is present in your classpath(add to project classpath if you are running this from Jdeveloper.) On successful connection you are shown the details of the server with an exit code of 0 and incase of failure you are shown some RFC errors like RFC_LOGON_FAILURE etc.

XSLT 2.0 Grouping & Sorting in 11g BPEL

I had a requirement recently where I had to split my incoming XML file(read from a database) based on a particular field and send the split sections to another BPEL process for further processing.

Here is how the Input xml looked like

 
  arr1
  ...
  ...
  
  
  arr2
  ...
  ...
  
  
  arr1
  ...
  ...
  
  
  arr2
  ...
  ...
  

Desired Output looks like this.

  
  arr1
  ...
  ...
  
  
  arr1
  ...
  ...
  


  
  arr2
  ...
  ...
  
  
  arr2
  ...
  ...
  

As can be seen above, the idea was to split the Input xml based on arr_id field into different collections and each collection having data for a particular arr_id. Once this is done, all I had to do is run a loop in my BPEL and for each collection send the data to the 2nd BPEL process.

I decided to use XSLT Grouping and Sorting feature to accomplish this task. If we are using XSLT 1.0 there is a particular way to do this using xsl:key which has been explained well in http://www.jenitennison.com/xslt/grouping/muenchian.html . However this can be done easily in XSLT 2.0 using xsl:for-each-group.

Below is the XSL file which does this task for us,

<xsl:stylesheet version="2.0" xmlns:ns1="http://xmlns.oracle.com/pcbpel/adapter/db/Select_Arrid" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
  <ns1:root>
    <xsl:for-each-group select="*/ns1:Select_Arrid" group-by="ns1:Arr_id">
        <xsl:sort select="current-grouping-key()">
        <ns1:select_arrid_collection>
           <xsl:copy-of select="current-group()">
                <xsl:apply-templates/>
          </xsl:copy-of> 
        </ns1:select_arrid_collection>
      </xsl:sort>
    </xsl:for-each-group>
   </ns1:root>
 </xsl:template>
</xsl:stylesheet>

Make sure the version of XSL is 2.0 for using the xsl:for-each-group construct. Now the xsl can be used in any transform activity and will give us the desired output. Hope this helps someone having similar XSLT grouping requirement in BPEL.

AIA Error Handling : Error Notifications/Emails

In continuation with my earlier posts on AIA Error handling, I will cover the steps required to enable/send error notifications in this post. AIA Error handling framework has out-of-the-box error email functionality. Incase of partner link errors like remote/binding faults the fault-policy.xml file should take care of sending the error notification/email. Incase of non-partner link error the AIAAsyncErrorHandlingBPEL process is called from catch-all block which inturn sends out the error email.

A sample fault-policy.xml file is shown below

3
2

In above file, the oracle.apps.aia.core.eh.CompositeJavaAction class file gets invoked when remote/binding fault occurs and takes care of sending the error notification to AIAIntegrationAdmin user.

A sample code for populating EBM Header in catch-all block is shown below

1. First create a variable named EBM_HEADER
<variable name="EBM_HEADER" element="ns3:EBMHeader"/>

ns3 here refers to corecom namespace.

2. Make sure your ABM_to_EBM.xsl or EBM_to_ABM.xsl are populating the EBM header section correctly (eg. EBMID, EBOName etc...)

3. In catch-all block make sure you are populating the EBM_HEADER before invoking the AIAAsyncErrorHandlingBPELProcess

<assign name="Assign_Fault">
           <copy>
            <from expression="ora:processXSLT('xsl/EBM_to_Fault.xsl',bpws:getVariableData('EBM_HEADER'))"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault"/>
          </copy>
          <copy>
            <from expression="ora:getFaultAsString()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultMessage/ns3:Text"/>
          </copy>
          <copy>
            <from expression="xpath20:current-dateTime()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:ReportingDateTime"/>
          </copy>
          <copy>
            <from expression="ora:getProcessId()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultingService/ns3:ID"/>
          </copy>
          <copy>
            <from expression="'BPEL'"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultingService/ns3:ImplementationCode"/>
          </copy>
          <copy>
            <from expression="ora:getInstanceId()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultingService/ns3:InstanceID"/>
          </copy>
          <copy>
            <from expression="ora:getECID()"/>
            <to variable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
                part="FaultMessage" query="/ns3:Fault/ns3:FaultNotification/ns3:FaultingService/ns3:ExecutionContextID"/>
          </copy>
        </assign>
      <invoke name="Invoke_AIAAsyncErrorHandlingBPELProcess" inputVariable="Invoke_AIAAsyncErrorHandlingBPELProcess_initiate_InputVariable"
              partnerLink="AIAAsyncErrorHandlingBPELProcess"
              portType="ns10:AIAAsyncErrorHandlingBPELProcess"
              operation="initiate"/>

Apart from this there are certain configurations which need to be done for successfully sending error emails.

1. Set the email driver properties from EM Console :

OutgoingMailServer :
OutgoingMailServerPort :
OutgoingMailServerSecurity :
OutgoingDefaultFromAddress:

2. In AIA_HOME/aia_instances/Instance_name/AIAMetaData/config/AIAConfigurationProperties.xml file , below properties should be set

<Property name="EH.INVOKE.NOTIFY">true</Property>
<Property name="FROM.EMAIL.ID">Email:AiaAdmin@oracle.com</Property>
<Property name="EH.DEFAULT.ACTOR.ROLE">AIAIntegrationAdmin</Property>

3. In User Messaging Preferences console (http://:/sdpmessaging/userprefs-ui) add email Id for user AIAIntegrationAdmin. Login to this console with AIAIntegrationAdmin/welcome1.

NOTE: Message channels are meant to be a single email address or phone number etc.But you can refer to an email distribution list if you want to send these emails to multiple persons.

4. Upload the AIAConfigurationProperties.xml file to MDS and Reload the configuration from AIA Console setup page.Make sure when you update UpdateMetaDataDP.xml for loading the AIAConfigurationProperties.xml into MDS it should look something like below:

<fileset dir="${AIA_HOME}/aia_instances/instance_name/AIAMetaData">
<include name="config/AIAConfigurationProperties.xml" />
</fileset>

Also if the Go buttons are not enabled on AIA Console Setup page, make sure the weblogic user has the role AIAApplicationUser associated with it. You can associate roles on the Weblogic Admin Console under Security Realms.

5. Also set Notification mode to 'Email' from 'SOA Administration' -> 'Workflow Notification Properties' in EM Console.

Error Handling in SAP Adapter

I have come across a couple of scenarios where even though data gets posted successfully to SAP via BAPIs, but the adapter still throws an error message. At the BPEL layer the adapter throws a generic error like  "Error in Processing Input Document"

Something similar to below is shown in JCA log files:

<RETURN>
      <item>
         <TYPE>S</TYPE>
         <ID>RW</ID>
         <NUMBER>605</NUMBER>
         <MESSAGE>Document posted successfully: 060014911815002010 PRDCLNT500</MESSAGE>
         <LOG_NO/>
         <LOG_MSG_NO>000000</LOG_MSG_NO>
         <MESSAGE_V1></MESSAGE_V1>
         <MESSAGE_V2>060014911815002010</MESSAGE_V2>
         <MESSAGE_V3>PRDCLNT500</MESSAGE_V3>
         <MESSAGE_V4/>
         <PARAMETER/>
         <ROW>0</ROW>
         <FIELD/>
         <SYSTEM>PRDCLNT500</SYSTEM>
      </item>
      <item>
        <TYPE>W</TYPE>
         <ID>KI</ID>
         <NUMBER>155</NUMBER>
         <MESSAGE>Profitability segment is derived as new</MESSAGE>
         <LOG_NO/>
         <LOG_MSG_NO>000000</LOG_MSG_NO>
         <MESSAGE_V1/>
         <MESSAGE_V2></MESSAGE_V2>
        <MESSAGE_V3/>
         <MESSAGE_V4></MESSAGE_V4>
         <PARAMETER/>
         <ROW>0</ROW>
         <FIELD/>
         <SYSTEM>PRDCLNT500</SYSTEM>
      </item>
   </RETURN>

MySAP response error: BapiWarning: Profitability segment is derived as new

IWAFManagedConnectionFactory com.ibi.sap.SapConnection rollback(266) Call BAPI_TRANSACTION_ROLLBACK

IWAFManagedConnectionFactory com.ibi.sap.SapAdapter20 inProcess(397) java.lang.Exception: BapiWarning: Profitability segment is derived as new
at com.ibi.sap.DocumentRunner.processIfrDocument(DocumentRunner.java:274)
at com.ibi.sap.SapAdapter20.inProcess(SapAdapter20.java:369
…
…

This ultimately rolls back to the error "Error in Processing Input Document" at BPEL layer.

To overcome this situation SAP adapter provides an option for error handling . From Application explorer if you edit the SAP target then there are 2 choices under Advanced tab : Throws Exception and Creates Error Document.

If you set it to "creates error document" and restart the server, the BAPI invocations should work correctly and above RETURN section should be shown in the BPEL layer as well.

BPEL Console fails with "ORA-28001: the password has expired"

I recently came across an issue while accessing BPEL console, and was shown a SQL Exception "ORA-28001: the password has expired" on the BPEL console page. On checking the Oc4j_soa log files found the same error:

java.sql.SQLException: ORA-28001: the password has expired
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:138)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:316)
...
...

On debugging further found that the orabpel and oraesb account passwords had EXPIRED on the Fusion DB. That was by design and implementation of the Oracle DEFAULT profile to which the users belonged. So to fix the issue the user accounts had to be unlocked/unexpired so that status showed as OPEN.

You can also check with your DBA team to see if the DEFAULT profile can be changed to allow unlimited days for a password and unlimited attempts at logging in (before account is locked).