Screencast 10 – EBS Order Entry with WebCenter via BPEL and SOA Gateway

Screencast 10 – EBS Order Entry with WebCenter via BPEL and SOA Gateway

The E-Business SOA Gateway supplies a way to expose a variety of different E-Business Suite information by way of PLSQL, Java and other assets in EBS. By default the SOA Gateway exposes a number of services, however these services can often have complex input parameters and can be cumbersome to interact with via direct web services calls. By using well known PL/SQL to interact with E-Business, simplified services can be exposed via the SOA Gateway. To then call into these services a mediation layer using portions of SOA Suite can make interaction straightforward per the following high level structure.

In our sample scenario we create a custom service, create a BPEL process that interacts with it and brokers authentication to the SOA Gateway, and finally consume the BPEL service in WebCenter to allow end users to place simple orders via an extranet. This example is meant to expose some key concepts and technical attributes, but does not represent a complete or production ready solution.

Ideally, organizations looking to implement this type of integration will examine Oracle solutions like the Application Integration Architecture Pre-built Integrations to see if a suitable fit exists for their use case.

To create our example, we leveraged the following assets were used

  • E-Business Suite 12.1 instance
  • VirtualBox appliance for Oracle SOA 11g and Oracle BPM 11g version 11.1.1.6
  • JDeveloper 11.1.1.6

Using these tools, we proceeded to work through the following areas of our architecture to complete our sample integration

  1. Creating a custom service using E-Business SOA Gateway to place a simple order
  2. Consuming the service with BPEL, passing sample Username Token security policy with request to the SOA Gateway
  3. Consuming the BPEL WSDL in JDeveloper and using it within a WebCenter extranet project

Creating a custom service using E-Business SOA Gateway to place a simple order

To set our foundation, we first needed to ensure that E-Business Suite’s SOA Gateway exposed a service that could allow us to place orders. Natively, the SOA Gateway does, but there is some complexity involved in the information required to invoke the service. Given that we just wanted a simple example, we leveraged the ability to register a custom service within the SOA Gateway, based on PL/SQl loaded into our E-Business schema. The following reviews the processes required for the registration of our custom service. The pattern can be used to extend to any aspect of E-Business.

Creating a Customer E-Business SOA Gateway Service

To register our service, we need to execute a series of command line functions to load our function into a format that E-Business will load and make available as a service in the SOA Gateway. The first step is providing an overview of the function in the form of a PLS file as follows.

PLS File
Provides the specifications needed for E-Business to understand our custom integration function.

xx_ebs_simple_order.pls

CREATE OR REPLACE PACKAGE xx_ebs_simple_order AS
/* $Header: $ */
/*#
* This package provides a function to creates a basic order in EBS as a proof
* @rep:scope public
* @rep:product oe
* @rep:displayname xx_ebs_simple_order
* @rep:lifecycle active
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY ONT_SALES_ORDER
*/

/*#
* Creates a simple order in EBS as a proof
* @param P_ITEMID varchar2 ItemID
* @return ORDERSTATUS
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname Create_SimpleOrder
*/
FUNCTION xx_create_simpleorder (P_ITEMID IN VARCHAR2) RETURN NUMBER;
END xx_ebs_simple_order;

Some of the lines in the above file are self explanatory, but others like the BUSINESS_ENTITY will let EBS how to categorize your custom integration. If you need to locate various options the following query can be made against the application schema using a tool like Oracle SQL Developer (http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html)

SELECT * FROM fnd_lookups WHERE lookup_type = 'BUSINESS_ENTITY'

Load Package and Functions
We now need to load this file into a format that E-Business can consume. Copy your xx_ebs_simple_order.pls file into $GL_TOP/patch/115/sql (in my demonstration instance this resolved to /oracle/r12/VIS121/apps/apps_st/appl/gl/12.0.0/patch/115/sql) and /tmp

Create an iLDT file for the Integration Repository with Repository Parser. Head into directory $IAS_ORACLE_HOME/perl/bin and run the following against simple order file

perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin gl:patch/115/sql:xx_ebs_simple_order.pls:12.0=/tmp/xx_ebs_simple_order.pls

You should see the following output

VIS121 r121@ebs121-vm /oracle/r12/VIS121/apps/tech_st/10.1.3/perl/bin $ perl $FND_TOP/bin/irep_parser.pl -g -v -username=sysadmin gl:patch/115/sql:xx_ebs_simple_order.pls:12.0=/tmp/xx_ebs_simple_order.pls
# Interface Repository Annotation Processor, 12.0.0

#
# Generating annotation output.
# Processing file '/tmp/xx_ebs_simple_order.pls'.
# Using YAPP-based parser.
#  Found a package-level annotation for 'XX_EBS_SIMPLE_ORDER'.
#  Found a detail-level annotation...
# Found a function named 'XX_CREATE_SIMPLEORDER'.
# Done all files.
VIS121 r121@ebs121-vm /oracle/r12/VIS121/apps/tech_st/10.1.3/perl/bin $

We now upload into E-Business as follows

$FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct xx_ebs_simple_order_pls.ildt

If all is successful, we will see the following result

VIS121 r121@ebs121-vm /oracle/r12/VIS121/apps/tech_st/10.1.3/perl/bin $ $FND_TOP/bin/FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/wfirep.lct xx_ebs_simple_order_pls.ildt
Log filename : L5827835.log


Report filename : O5827835.out
VIS121 r121@ebs121-vm /oracle/r12/VIS121/apps/tech_st/10.1.3/perl/bin $

Load Package and Functions into Database
Now that E-Business is aware of the specification of our function, we need to actually load it into the schema as follows

  1. Open SQLDeveloper
  2. Open 1-XX_EBS_SAMPLE_ORDER_Specification.sql
  3. Select Run Script (F5) 1-XX_EBS_SAMPLE_ORDER_Specification.sql
  4. Open 2-XX_CREATE_SIMPLEORDER_Package_and_Function.sql
  5. Select Run Script (F5) 2-XX_CREATE_SIMPLEORDER_Package_and_Function.sql

The source files are reproduced below

1-XX_EBS_SAMPLE_ORDER_Specification.sql

CREATE OR REPLACE PACKAGE xx_ebs_simple_order AS
/* $Header: $ */
/*#
* This package provides a function to creates a basic order in EBS as a proof
* @rep:scope public
* @rep:product oe
* @rep:displayname xx_ebs_simple_order
* @rep:lifecycle active
* @rep:compatibility S
* @rep:category BUSINESS_ENTITY ONT_SALES_ORDER
*/

/*#
* Creates a simple order in EBS as a proof
* @param P_ITEMID varchar2 ItemID
* @return ORDERSTATUS
* @rep:scope public
* @rep:lifecycle active
* @rep:displayname Create_SimpleOrder
*/
FUNCTION xx_create_simpleorder (P_ITEMID IN VARCHAR2) RETURN NUMBER;
END xx_ebs_simple_order;

If the script ran successfully, you should see a message similar to the following “PACKAGE XX_EBS_SIMPLE_ORDER compiled”.

2-XX_CREATE_SIMPLEORDER_Package_and_Function.sql
The following script takes 2 parameters, one for quantity, the other for an Item ID that maps back to an inventory ID in EBS. Values that are barcoded below and done on the basis of the sample instance that was used for this demonstration. These values will vary by environment. If you ever need to locate the values that make sense for your instance, place a test order and review the results that are saved for that particular test order.

CREATE OR REPLACE
PACKAGE BODY xx_ebs_simple_order AS

FUNCTION xx_create_simpleorder (P_ITEMID IN VARCHAR2, P_QTY IN NUMBER) RETURN NUMBER IS

l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
/*****************PARAMETERS****************************************************/
l_debug_level number := 5;
l_org number := 204;
l_user number := 1318;
l_resp number := 50602;
l_appl number := 660;
l_postamp varchar(100) := ('WC-Order-' || to_char(sysdate, 'hh24-mi'));
/***INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
/***OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
-- book API vars

b_return_status VARCHAR2(200);
b_msg_count NUMBER;
b_msg_data VARCHAR2(2000);
BEGIN
dbms_application_info.set_client_info(l_org);
/*****************INITIALIZE DEBUG INFO*************************************/
if (l_debug_level > 0) then
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(l_debug_level);
Oe_Msg_Pub.initialize;
end if;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize(l_user, l_resp, l_appl); -- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
/***********POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := 1437;
l_header_rec.sold_to_org_id := 1290;
l_header_rec.ship_to_org_id := 1425;
l_header_rec.price_list_id := 1000;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := 'USD';
l_header_rec.flow_status_code := 'ENTERED';
l_header_rec.cust_po_number := l_postamp;
l_header_rec.order_source_id := 0;
--l_header_rec.attribute1 := 'ABC';
/*******INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl(1) := oe_order_pub.G_MISS_REQUEST_REC;
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
/*****************INITIALIZE LINE RECORD********************************/
l_line_tbl(1) := oe_order_pub.G_MISS_LINE_REC;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
l_line_tbl(1).inventory_item_id := P_ITEMID;
l_line_tbl(1).ordered_quantity := P_QTY;
l_line_tbl(1).ship_to_org_id := 1425;
l_line_tbl(1).tax_code := 'Location';
/*****************CALLTO PROCESS ORDER API*********************************/
dbms_output.put_line('Calling API');
oe_order_pub.Process_Order( p_api_version_number => '1',
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
--OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);

/*****************CHECK RETURN STATUS***********************************/
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Return status is success ');
  RETURN l_header_rec_out.order_number;
dbms_output.put_line('debug level ' || l_debug_level);
if (l_debug_level > 0) then
dbms_output.put_line('success');
end if;
commit;
else
dbms_output.put_line('Return status failure ');
  RETURN -1;
if (l_debug_level > 0) then
dbms_output.put_line('failure');
end if;
rollback;
end if;

/*****************DISPLAY RETURN STATUS FLAGS******************************/
if (l_debug_level > 0) then
 DBMS_OUTPUT.PUT_LINE('process ORDER ret status IS: '|| l_return_status);
DBMS_OUTPUT.PUT_LINE('process ORDER msg data IS: '|| l_msg_data);
DBMS_OUTPUT.PUT_LINE('process ORDER msg COUNT IS: '|| l_msg_count);
DBMS_OUTPUT.PUT_LINE('header.order_number IS: '|| to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE('header.return_status IS: '|| l_header_rec_out.return_status);
DBMS_OUTPUT.PUT_LINE('header.booked_flag IS: '|| l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE('header.header_id IS: '|| l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE('header.order_source_id IS: '|| l_header_rec_out.order_source_id);
DBMS_OUTPUT.PUT_LINE('header.flow_status_code IS: '|| l_header_rec_out.flow_status_code);
end if;
/*****************DISPLAY ERROR MSGS*************************************/
if (l_debug_level > 0) then
FOR i IN 1 .. l_msg_count LOOP
Oe_Msg_Pub.get(
p_msg_index => i
,p_encoded => Fnd_Api.G_FALSE
,p_data => l_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE('message is: '|| l_data);
DBMS_OUTPUT.PUT_LINE('message index is: '|| l_msg_index);
END LOOP;
end if;
if (l_debug_level > 0) then
DBMS_OUTPUT.PUT_LINE('Debug = ' ||OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE('Debug Level = '|| to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE('Debug File = '|| OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);
DBMS_OUTPUT.PUT_LINE('****************************************************');

OE_DEBUG_PUB.DEBUG_OFF;
end if;

END xx_create_simpleorder;
END xx_ebs_simple_order;

If the script ran successfully, a message similar to the following will be displayed “PACKAGE BODY XX_EBS_SIMPLE_ORDER compiled”. Now that our function is loaded, let’s locate and test it through the SOA Gateway.

Using Our Custom SOA Gateway Service
Start by logging into EBS as a user who can access the SOA Gateway.

Navigate to Order Management Suite -> Order Management -> Sales Order.

Generate WSDL
If successful, the following message will be displayed “Successfully generated Web Service for interface ‘xx_ebs_simple_order’”.

Grant permissions
Under “Authentication Type” select “Username Token”, then select “Deploy”.

Under “Procedures and Functions” select our service, then click “Create Grant”. Since we are using this as a proof, set the “Grantee Type” to “All Users” and then select “Apply”.

Once back on the main screen for our service, select the “View WSDL” link. It should open a new browser window at an address similar to http://ebs121-vm.us.oracle.com:8000/webservices/SOAProvider/plsql/xx_ebs_simple_order/?wsdl where we can view the WSDL based on our original PL/SQL service.

You may need to restart the application server or clear the cache in order for the service to function properly. If you encounter any errors about access to the service, but believe that you have correctly applied a Grant, remove and re-apply the grant.

Consuming the service with BPEL, passing sample Username Token security policy with request to the SOA Gateway

In our example BPEL provided a few key capabilities to support interaction with EBS – brokering security to SOA Gateway, tracing of the process involved in building and executing our service call to the EBS SOA Gateway, asynchronous request handling to eliminate the dependance of our UI on any order processing latency (our sample does leverage synchronous through for us to confirm an order ID), and pre-population of a number of attributes in the method we expose via EBS SOA Gateway, so that our WebCenter code only needs to deal with a simple service.

For people new to BPEL, the following is a good tutorial that provides a basic overview – http://www.oracle.com/technetwork/topics/bpel-hello-world-128219.pdf. We will not exhaustively cover BPEL here and each step in creation of our BPEL process, but the following were required to support our proof. The complete project code is included at the end of this post for reference.

WSS Username Token Client Policy in composite.xml
In order to communicate with EBS via our service call, we will need to pass along some security information. In a real, production environment, we would probably choose to leverage SAML across our system to manage authorization. In our case, we simply would like to give enough information in the headers of our SOAP request to EBS for it to authenticate for testing purposes.

In prior versions of BPEL, it was possible to add this header information through some property screens. As this is perhaps not a best practice, they 11g version used no longer will allow that, so we add the following to our composite.xml to insert a static oracle/wss_username_token_client_policy policy for our tests. You will need to edit the XML of the composite directly to add the following (or something similar based on your environment).

     <reference name="EBSServices"
             ui:wsdlLocation="http://ebs121-vm.us.oracle.com:8000/webservices/SOAProvider/plsql/xx_ebs_simple_order/?wsdl">
    <interface.wsdl interface="http://xmlns.oracle.com/apps/oe/soaprovider/plsql/xx_ebs_simple_order/#wsdl.interface(XX_EBS_SIMPLE_ORDER_PortType)"/>
    <binding.ws port="http://xmlns.oracle.com/apps/oe/soaprovider/plsql/xx_ebs_simple_order/#wsdl.endpoint(XX_EBS_SIMPLE_ORDER_Service/XX_EBS_SIMPLE_ORDER_Port)"
                location="http://ebs121-vm.us.oracle.com:8000/webservices/SOAProvider/plsql/xx_ebs_simple_order/?wsdl"
                soapVersion="1.1">
      <wsp:PolicyReference URI="oracle/wss_username_token_client_policy"
                           orawsp:category="security" orawsp:status="enabled"/>
        <property name="weblogic.wsee.wsat.transaction.flowOption" type="xs:string" many="false">WSDLDriven</property>
        <property name="oracle.webservices.auth.username" type="xs:string" many="false" override="may">sysadmin</property>
        <property name="oracle.webservices.auth.password" type="xs:string" many="false" override="may">sysadmin</property>
    </binding.ws>
  </reference>

Once the BPEL process is configured you can being to test using the native interface available via the interface in OEM over our process. This will allow you to add an argument to set a parameter to pass into your process and map into the call that BPEL will make into the SOA Gateway. It is a good way to verify that the service is working before starting to use the WebCenter interface on top of the services.

Consuming the BPEL WSDL in JDeveloper and using it within a WebCenter extranet project

For anyone with familiarity around ADF, this portion of the exercise is trivial. Now that we have our BPEL process up and running, we can connect to a WSDL that exposes the process and offers an attribute for us to pass into our exposed method to place our order. As a side note, from my experience the BPEL process can only have a single input parameter that is of a simple type (it can support complex input parameter types though), so for simplicity, we are going to pass the inventory ID for a given EBS item into our method to place our order.

Code Download

Resources

0 comments