Tuesday, March 31, 2015

Converting SAP IDoc XML date and time to a database date value

In one of my last blog entries, I descriped how to convert a xsd:string date and time value from SAP IDoc into the more appropriate xsd:dateTime value. While for most cases, specifying the required date and time parameters of xsd:dateTime is sufficient, it might not for database writes. Databases often require the precision of the optional milliseconds and especially timezone parameters of xsd:dateTime when writing the a DATE field.

The following POC will show how to extend the XSL Transformation to add entirely specify all parameters of xsd:dateTime. The POC is realizing a database adapter service to write a new empoyee to the well-known HR schema. Therefore, a BPEL process is exposing a SOAP-based web service endpoint to provide input data in a SAP IDoc like style ... hence all data are defined as xsd:string, no matter their contents' meaning.

SCA composite of the POC
The input data require firstname, lastname and hire date and time. The web service returns the hire date formated as xsd:dateTime after successful insert to the database.
XML Schema Definition of input and output data
The BPEL process itselfs stores the received input to an input variable. The following XSL transformation we focus on maps the input data to the database write variable. After successful write to the database by invocation of the endpoint reference of the database adapter, the returned data are mapped to the output data of the process.

BPEL Process "AddEmployee"
Let's have a detailed look at the XSL transformation that is "building" our xsd:dateTime datatype. The graphical representation already shows the usage of the xp20:implicit-timezone() function from XPath 2.0 (green icon).

XSL transformation to "build" an xsd:dateTime value
While the date and time concatination is known from my previous poc, we will focus on the milliseconds and timezone parameters. The milliseconds are simply set to zero (.000) while we make use of the service engines' timezone setting to specify the timezone. In the end, we end up with a pattern of yyyy-mm-ddThh:mm:ss.s+zzzzzz instead of the less precise pattern yyyy-mm-ddThh:ss:mm from the previous POC. A detailed description of the pattern can be found in the W3C recommendation of XSD.

<top:hireDate>
  <xsl:value-of select='concat(
    substring(/client:request/client:hireDay,1.0,4.0),"-",
    substring(/client:request/client:hireDay,5.0,2.0),"-",
    substring(/client:request/client:hireDay,7.0,2.0),"T",
    substring(/client:request/client:hireTime,1.0,2.0),":",
    substring(/client:request/client:hireTime,3.0,2.0),":",
    substring(/client:request/client:hireTime,5.0,2.0),".000",
    xp20:implicit-timezone())'/>
</top:hireDate>

Testing the XSL transformation in the XSLT tester in JDeveloper, everything is working as expected.

Local testing in JDevelopers' XSLT tester

Testing the POC in Oracle Enterprise Manager shows everything working as expected.

Testing the POC in Oracle Enterprise Manager with sample data

An xsd:dateTime value with full precision is returned

References:

Node selection of Oracle's XSLT engine

Oracle Busisness Process Management Suite and Oracle SOA Suite enable XML Stylesheet Transformations (XSLT). The XSLT engine is part of the mediator component engine, which is indeed the former enterprise service bus (ESB) implementation of Oracle before Oracle Service Bus (OSB) was bought.
For implementation purpose, it is sometimes useful to know exactly the node selection behavior of the XSLT engine. In most cases, XSLT relies on XML Path Language (XPath) node selection mechanisms. But what happens, if the expression would match more then one node? Well, let's try!

The POC is quiet simple. A synchornous BPEL process exposing a SOAP web service interface accepts a list of animals as request.

SCA Composite of the POC

For the response, a list of the first, last and XPath node-selected animal will be returned.

XML Schema definition of request and response of the SOAP web service endpoint

The BPEL process itself is quiet simple: Received input data from the input variable are transformed to the output variable to be returned to the requester.

BPEL Process "SortMyAnimals"


The XSL Transformation uses XPath expression to map the first animal of the input list (/request/animals/animal[1]), the last animal of the input list (/request/animals/animal[last()]) and the animal that is selected by the XSLT engine we are actually interested in (/request/animals/animal) ... since we did not define its position in the list.

  <xsl:template match="/">
    <ns1:response>
      <ns1:firstanimal>
        <xsl:value-of select="/ns1:request/ns1:animals/ns1:animal[1]"/>
      </ns1:firstanimal>
      <ns1:lastanimal>
        <xsl:value-of select="/ns1:request/ns1:animals/ns1:animal[last()]"/>
      </ns1:lastanimal>
      <ns1:nodeselectedanimal>
        <xsl:value-of select="/ns1:request/ns1:animals/ns1:animal"/>
      </ns1:nodeselectedanimal>
    </ns1:response>
  </xsl:template>


For a sample input of three animals cat, dog and cow (in that order), the XSLT tester in JDeveloper returns the cat, that means the first element that is matching the XPath expression.

Local testing in JDeveloper using XSLT tester
After deployment of the POC to our environment, we will run the same example to proof what we found out.

Testing the POC in Oracle Enterprise Manager
 And yes, the result is the same:

Testing result in Oracle Enterprise Manager

Summery: Not specifying the exact node in an XPath expression in an XSL Stylesheet Transformation will return the first node matching the path criteria.

References:

Sunday, March 22, 2015

Converting SAP IDoc XML date and time to structured XML Schema xsd:dateTime data type

In a recent project, we worked with the Oracle Application Adapter for SAP R/3 in order to retrieve data from and publish data to the customers SAP system. Did you ever have a look at an XML-based intermediate document (IDoc) from SAP? Well, good news is that you get structured data. Each IDoc type comes with a schema definition as XML Schema Definition (XSD) which can be retrieved from the SAP system using Oracle Application Explorer. Bad news is that absolutly everything is stored as xsd:string. No matter the content of the element, if it's some numeric values, text, date or time values ... the values are stored as string. SAP doesn't make use of the more appropriate default datatypes or the XML Schema Definition.
However, other sources do. So if you want to use for example the header data of an XML IDoc DC40 fragment to call a web service, you need to transform the string based date value CREDAT and the string based time value CRETIM to the more appropriate xsd:dateTime.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            targetNamespace="urn:sap-com:document:sap:idoc"
            xmlns="urn:sap-com:document:sap:idoc"
            elementFormDefault="qualified">
   <xsd:element name="IDOCTYPEOFYOURCHOICE">
      <xsd:complexType>
         <xsd:sequence>
            <xsd:element name="IDOC" maxOccurs="unbounded" minOccurs="0">
               <xsd:complexType>
                  <xsd:sequence>
                     <xsd:element name="EDI_DC40">
                        <xsd:complexType>
                           <xsd:sequence>
                              ...
                              <xsd:element name="CREDAT" minOccurs="0">
                                 <xsd:simpleType>
                                    <xsd:restriction base="xsd:string">
                                       <xsd:maxLength value="8"/>
                                    </xsd:restriction>
                                 </xsd:simpleType>
                              </xsd:element>
                              <xsd:element name="CRETIM" minOccurs="0">
                                 <xsd:simpleType>
                                    <xsd:restriction base="xsd:string">
                                       <xsd:maxLength value="6"/>
                                    </xsd:restriction>
                                 </xsd:simpleType>
                              </xsd:element>
                              ...
                           </xsd:sequence>
                           ...
                        </xsd:complexType>
                     </xsd:element>
                     ...
                  </xsd:sequence>
                  ...
               </xsd:complexType>
            </xsd:element>
         </xsd:sequence>
         ...
      </xsd:complexType>
   </xsd:element>
</xsd:schema>


You can achieve this by assigning string data in the correct pattern to an xsd:dateTime element. This is possible since xsd:dateTime is accepting string inputs. However, the pattern of xsd:dateTime has to be respected. For our example, it is enough to target the pattern "yyyy-mm-ddThh:mm:ss" without the optional milliseconds and time zone parameters. Check out detailed information in XML Schema Definition Part 2 Datatypes.

The little POC for this is based on a synchronous BPEL process waiting for input data similar to XML IDoc data. An XSL Transformation is used to transform the string-based input data to xsd:dateTime data. Additionally, the XSL engine will add a generated xsd:dateTime value of the runtime environment. The following figure shows the BPEL process.

BPEL process GenerateTime
The service component architecture diagram is as simple as that, showing the exposed service endpoint and the BPEL process.

SCA composite overview of the POC
The input data to the request contains two elements date and time. Both of them are simple types based on xsd:string and restricted in length of 8 respectively 6 characters to represent a date of patter "yyyymmdd" and time of pattern "hhmmss".

XML Schema definition of the POC
The response data on the other hand contains two xsd:dateTime elements. One of them will be the target of the XSL transformation while the other one will hold engine generated data.

The following screenshot is taken from the XSLT editor in JDeveloper with SOA extension.

XSL Transformation is XSLT editor
We are using substrings to extract the year, month and day fragments from the input string date. The same way, we are extracting the hour, minute and second fragments from the input time value. Afterwards, we are concatinating all fragments including the required separators to the desired pattern of the xsd:dateTime based value ns1:customDateTime. Please find below the source code.

  <xsl:template match="/">
    <ns1:response>
      <ns1:customDateTime>
        <xsl:value-of select="concat(

          substring(/ns1:request/ns1:date,1.0,4.0),'-',
          substring(/ns1:request/ns1:date,5.0,2.0),'-',
          substring(/ns1:request/ns1:date,7.0,2.0),'T',
          substring(/ns1:request/ns1:time,1.0,2.0),':',
          substring(/ns1:request/ns1:time,3.0,2.0),':',
          substring(/ns1:request/ns1:time,5.0,2.0))"/>
      </ns1:customDateTime>
      <ns1:generatedDateTime>
        <xsl:value-of select="xp20:current-dateTime()"/>
      </ns1:generatedDateTime>
    </ns1:response>
  </xsl:template>



Now let's test the process (and XSL transformation) from Enterprise Manager, providing some sample data ...

Input data for testing
... and we are successfully receiving the transformed data as response:

Output data of the test
Notice that our transformed xsd:dateTime value only comes with the precision of the provided input data while the generated timestamp comes with the complete, detailed xsd:dateTime pattern including the optional parameters of milliseconds and time zone.

Here you can download the complete POC.

Tuesday, September 9, 2014

Testing your REST service in JDeveloper - How to access the sample data form

Oracle SOA Suite 12c does not only come around with support for RESTful Web services, but also provides extensive capabilities to test Web services directly from JDeveloper.

When trying to test a RESTful Web service endoint based on the Web Application Description Language (WADL) file, it is a bit less obvious what to do.

  1. Get the WADL from Enterprise Manager. Your WADL will look similar to https://localhost:7102/soa-infra/resources/default/ValidatePayment!1.0/validatePaymentRESTService/application.wadl where ValidatePayment is the SCA application name, 1.0 is the version of the SCA application and validatePaymentRESTService is operation name.
    Get WADL for RESTful Web service from Enterprise Manager
  2.  In JDeveloper, select Tools > HTTP Analyzer
  3. Create a new REST request in the HTTP Analyzer
    Create New REST Request in HTTP Analyzer
  4. Paste WADL to the URL input field and confirm by tabbing out of the line. You will end up with the WADL URI and the  Operations available on the WADL ... which is actually only to retrieve the WADL content.
    Request to retrieve WADL
  5. This is actually not what we want. What we expect to get a a form where we can enter test values for all (or at least some) of the parameters of the REST operation. What you actually need to do to get the WADL analyzed and a form created is to edit the WADL. Remove the last part of the WADL, in my case application.wadl.
    Manipulate WADL URI
  6. Confirm and you will end up with the expected form.
    Form to test endpoint of a RESTful Web service
  7. Including parameters and parameter values will add those to the URL of the HTTP request.
    Example of HTTP request parameterization
  8. Enjoy testing from JDeveloper!

Property Window of Pipeline Component not showing in JDeveloper 12c

Oracle SOA Suite 12c introduces a new development environment for Oracle Service Bus (OSB). While OSB development was supported by the web-based SB Console and an Eclipse Plugin in 11g, the development has been moved to JDeveloper in 12c. This is done by introduction of a new OSB editor, which looks quite similar to the SCA application composite editor.
OSB Editor in JDeveloper 12c


Externally exposed Proxy Services (PS) are shown on the left column while the Business Service (BS) references show up on the right column ("External Services"). The central column shows OSB Resources such as Pipelines and Split/Joins. The above example shows an externally exposed SOAP Web service ("ProcessPS") referring to a SOAP based Web service ("ProcessBS") wired by a pipeline (ProcessPP).The new Pipeline Editor is actually used to configure this central OSB resource.
Pipeline Editor in JDeveloper 12c
The above screenshot shows the ProcessPP pipeline from the above example. When selecting a component of the pipeline, in the properties window below all properties to be defined will appear.
Properties of a Pipeline Component in JDeveloper 12c Pipeline Editor
However, sometimes the properties will not show up when selecting the pipeline component in the pipeline editor.
Missing Properties of Pipeline Component in JDeveloper 12c Pipeline Editor
This behavior has been reported with JDeveloper 12c installed with QuickStart on a Microsoft Windows 7 x64 machine running JDK 1.7.0 Update 67 (x64). The properties windows actually does not capture the selection of the component in the main windows. The workaround is simply to close and reopen JDeveloper ... which might be annoying if you are running the integrated WebLogic Server.

Monday, August 18, 2014

ACM Stakeholder and SCA Organization out of sync

Upon case design for Oracle Adaptive Case Management 12c, included in Oracle Business Process Management 12c, you need to take care that the application role table (organization.xml) of the BPM/ACM service component application and case stakeholder table of the case.

In Oracle BPM Studio 12c, create a new SCA application, e.g. called MyAcmApplication.
Add a BPM project (e.g. MyAcmProject) whose composite contains case management.
Since you selected Composite with Case Management, you will be asked to provide a case name, e.g. MyCase.
Now check for the details of organization.xml of the BPM project from the Applications navigator.
Applications navigator in Oracle BPM Studio 12c

(If the Applications navigator is not already open, in 12c you can find it under Windows menu from the main navigation of BPM Studio. This is different from 11g where it was under View menu.)
Once organization.xml opened, you will notice that so fare, ony the Process Owner and the Process Reviewer application role are present for this project.
Now, go to the Stakeholder property page of the case. Open the case file from the Applications navigator (MyAcmProject > BPM > Case > MyCase). Add a new stakeholder (e.g. MyStakeholder) to the stakeholder list. Don't forget to save.
New stakeholder added to case
Switch back to Organizations tab and notice that the new stakeholder has also been added as BPM application role.
Now, go back to the case definition and delete the prior created stakeholder. Save all. Switch back to the BPM application roles. You will notice that the according application role for your stakeholder did not get deleted aswell. At this point, you are out of sync between you case definitions stakeholders and you BPM applocation roles.

Since BPM application roles are global in the OracleBPMProcessRolesApp, this makes somehow sence to prevent deletion active application roles on redeployment of BPM/ACM applications. On the other hand, deployment won't ever delet application roles from OracleBPMRolesApp. Only updation association of user and groups is possible. Therefore, during design time, this behavior is somehow unexpected.
In the end, it is no big deal since you can still manually delete the application roles. This can be done prior to deployment (so that the application roles doesn't even get propagated to OracleBPMProcessRolesApp) or on runtime using BPM Workspace. It is just something you need to be aware of.

Friday, April 25, 2014

Upgrade your Oracle BPM Studio 11g (11.1.1.7)

Just in case some of you might have missed this: When Oracle BPM 11g's MLR3 came out (finally providing a basic OOTB UI for Case Management included in the Business Process Workspace application), also Oracle BPM Studio 11g (hence: Oracle JDeveloper with the extensions SOA Composite Editor and BPMN Editor) got an update:

  • SOA Composite Editor: 11.1.1.7.0.00.08 11.1.1.7.0.00.97
  • BPMN Editor: 11.1.1.7.0.0.8 11.1.1.7.0.0.97
For example, this upgrade includes components to work with Case Management UI, e.g. to generate case data.

Just upgrade your JDeveloper with the integrated update functionality Help > Check for updates ... and reduce the selection in step 3 to Shop upgrades only.

You cannot upgrade both extensions the same time (unless JDeveloper won't warn you on that). Make sure to first upgrade the SOA extension. (Otherwise, when first upgrading BPM, you won't be able to apply the SOA update afterwards.)

You can also download the extension sources first and upgrade the extensions with the integrated functionality Help > Check for updates ... and the Install from local file in step 2.