Introduction

Oracle provides a set of pre-built OIC integrations for connecting various modules in the SCM Cloud. One of the existing pre-built integrations is shipment request from Inventory Cloud to Warehouse Management System (WMS) Cloud. As an enhancement to the current prebuilt, we are adding a feature to the existing shipment request prebuilt to support shipment requests with shipment sets. This new feature requires a data mapping from a flat JSON Inventory data structure to a hierachical XML WMS data structure. The implementation is further complicated by data pagination which is a necessary measure for handling high volume shipment lines.  

Assumptions

The discussion below is based on an assumption that shipment request lines are retrieved from Inventory via REST API based on the query parameters below.

q=PickWaveId=213887 and ShipmentSet is not null, orderBy=ShipmentSet,ShipmentLine

The resulting payload data contains only shipment request lines that are in shipment sets and are sorted based on the ShipmentSet name and ShipmentLine number. A breviated sample JSON payload is shown below.

Data Mapping

Single-Page

The existing pre-built does not support shipment set. It transforms each shipment request line into a separate WMS order with a single line. A shipment set is a group of order lines that are required to be shipped together. WMS requires all lines in a shipment set be grouped into a single WMS order, which includes one order header element and multiple order detail elements. Each order detail element represents one shipment request line in the shipment set. The following picture shows two data structures side by side. The table on the left is an example of an Inventory shipment request payload where all shipment lines with a shipment set value are contained in a single response payload from the REST API. Note, the actual Inventory payload is in the JSON format. The tabular representation is for easy comparison. The XML data on the right is the result of transformation from the Inventory payload.

Multi-Page

When a shipment request involves a large number of shipment lines, REST API query for shipment lines from Inventory will need to be paginated for reliability and performance considerations. As a result, shipment lines in one shipment set might be split into several pages. In such scenarios, the split shipment set will be transformed into two WMS orders, the first WMS order from page 1 and the second order from page 2. These two WMS orders need to be merged into a single WMS order. In addition, their order detail sequence numbers need to be unique. The following image illustrates the data mapping.

The resulting XML payload from page 1 and page 2 are shown below.

Transformation Implementation in OIC

Turning the JSON shipment line request payload from Inventory into WMS XML orders payload is a multi-step process.

Step 1

The first step is to transform a page of JSON payload into XML. We can achieve this transformation in the follow XSLT snippet.

<ns29:ListOfOrders>
  <xsl:for-each-group select="$invokeGetShipmentLines/nsmpr1:executeResponse/nsmpr0:response-wrapper/nsmpr0:items" group-by="nsmpr0:ShipmentSet">
    <xsl:variable name="tempSalesOrder" select="concat(nsmpr0:Order[1],&quot;-Shipset:&quot;,nsmpr0:ShipmentSet[1])"/>
    <ns29:order>
      <ns29:order_hdr>
        ...
        <ns29:order_nbr><xsl:value-of select="nsmpr0:ShipmentLine[1]"/></ns29:order_nbr>
        ...
        <ns29:sales_order_nbr><xsl:value-of select="$tempSalesOrder"/></ns29:sales_order_nbr>
        ...
      </ns29:order_hdr>
      <xsl:for-each select="current-group()">
        <ns29:order_dtl>
          <ns29:seq_nbr>
            <xsl:choose>
              <xsl:when test="$lastWmsSalesOrder = $tempSalesOrder">
                <xsl:value-of select="position()+ number($lastSequenceNbr)"/>
              </xsl:when>
              <xsl:otherwise><xsl:value-of select="position()"/></xsl:otherwise>
            </xsl:choose>
          </ns29:seq_nbr>
          ...
          <ns29:ship_request_line><xsl:value-of select="nsmpr0:ShipmentLine"/></ns29:ship_request_line>
          ...
        </ns29:order_dtl>
      </xsl:for-each>
    </ns29:order>
  </xsl:for-each-group>
</ns29:ListOfOrders>

In the code snippet above, the variable lastWmsSalesOrder contains the last WMS sales order in the previous page. The variable lastSequenceNbr is the sequence number of the last line in the last WMS sales order in the previous page. These variables are necessary to determine the correct sequence number of an order details.

Because of all shipment lines from Inventory are sorted on ShipmentSet, only the last WMS sales order in the output XML can have split lines in the next page. Those split lines will only be in the first WMS sales order in the next page. So we will have to wait for the next page being transformed into XML in order to check for split shipment set.

Step 2

After the second page is transformed into XML, we can check for split shipment set by comparing the last sales order in page 1 to the first sales order in page 2 with the following XSLT code.

         $lastWmsSalesOrder = $currentWms_ListOfOrders/ns16:ListOfOrders/ns16:order[1]/ns16:order_hdr/ns16:sales_order_nbr

Step 3

If the test in Step 2 is true, we have a split shipment set. So we will need to move the order details in the first sales order in page 2 into the last sales order in page 1. To do this, we need to use the Data Stitch activity in OIC. We first append the order details from page 2 to page 1:

Then the first sales order in page 2 needs to be removed.

Conclusion

After merging of shipment lines and removal of duplicate sales order, we have completed transformation of the first page into WMS shipment request payload. We can send the first page to WMS. For the second page, we will have to wait for the next page to check for split shipment set.