Oracle E-Business Suite Blog

 

Context

More and more industries today are integrating and embracing Service Oriented Approach to do their business. During our recent rollout of one of such projects, we found ourselves in the same situation where the client was on EBS 11.5.10 and the vendor was offering web services as touch points for data handshake. Client additionally wanted to schedule this integration on daily basis.

We explored few options like CSV file sharing, DB links etc but none of them were feasible as vendor only offered web service. So should our client buy SOA Suite to integrate with vendor? Or else how will they call a web service?


Bridging the gap
The webservices are exposed to the external parties via a WSDL (Web Services Description Language). This WSDL is provided to the external partner. So the question now is how to use this WSDL from Oracle EBS?

From within EBS, we can call this web service via WSDL using Java or PL/SQL. In our case, we used a PL/SQL procedure to invoke the webservice as it suited the requirement better. To simply the actual business case, let us consider a simple scenario where a supplier responds back with the Inventory Quantity when presented with a Item Code. If the web service exposed by the supplier is called InvQty, the PL/SQL procedure to invoke this web service will be as follows:

CREATE OR REPLACE
PROCEDURE  CALL_INV_QTY_WS
IS

        soap_request varchar2(30000);
        soap_respond varchar2(30000);
        http_req utl_http.req;
        http_resp utl_http.resp;
        launch_url varchar2(240) ;

BEGIN

         soap_request:='<?xml version="1.0" encoding="UTF-8"?>

        <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

        <soap:Header/>

        <soap:Body xmlns:ns1="http://xmlns.oracle.com/InvQty_jws/Project1/InvQty">

                <ns1:process><ns1:ItemCode>MS787</ns1:ItemCode></ns1:process>

        </soap:Body>

        </soap:Envelope>';

        http_req:= utl_http.begin_request('http://soabpm-vm:7001/soa-infra/services/default/Project1/InvQty_client_ep','POST','HTTP/1.1');
        utl_http.set_header(http_req, 'Content-Type', 'text/xml') ;
        utl_http.set_header(http_req, 'Content-Length', length(soap_request)) ;
        utl_http.set_header(http_req, 'SOAPAction', 'process');
        utl_http.write_text(http_req, soap_request) ;
        http_resp:= utl_http.get_response(http_req) ;
        utl_http.read_text(http_resp, soap_respond) ;
        utl_http.end_response(http_resp) ;
        dbms_output.put_line(soap_respond);

EXCEPTION WHEN UTL_HTTP.end_of_body THEN

        utl_http.end_response(http_resp);

END;

This PL/SQL procedure thus embeds a SOAP (protocol for exchanging the structured information in the implementation of web service) call in the procedure's body. The web service is thus invoked via the procedure CALL_INV_QTY_WS.
Now with the basic code in place, we just need to schedule it!
Based on when the web service needs to be invoked, this procedure can be scheduled in any of the following ways:

1. System scripts such as .bat or .sh invoking with PL/SQL procedure
2. Using DBMS_JOB package
3. Oracle EBS Concurrent Program


Conclusion

While the above scenario is a simple use case, the other business scenarios may be more complex. With Oracle SOA Suite 11g, these complex scenarios can be handled with ease. Further, the transaction management, security and exception handling features in Oracle SOA Suite are capable of enhancing the message delivery and integrity. With a rich Fusion Middleware stack, Oracle completely addresses interaction both within and outside of the Oracle technologies and offers umpteen prebuilt adapter for FTP, Database, EBS, Message Queues etc.

 

Comment (0)

After some additional testing, the Entitlement Balances functionality is working fine.  One of our testers discovered note 473272.1 on Metalink which appeared to be similar to our problem.  The issue was resolved by extending our payroll calendar out by another two years. Oracle has logged Bug 5743838 in which part of this issue is described.

The screen shot below show a successful calculation in June 2010 after the extension of the payroll calendar.

Comment (0)

  Release 12.1 includes a useful addition to Absence Management. Employees and Managers can now use Self Service to calculate leave accruals based on an effective date. The new tab in the Absence Management function, named "Entitlement Balances", is shown below.

A user simply inputs the desired date and hits the "Go" button.  The accrual is calculated as of the date entered.  This is useful for both employees and managers wishing to view accrual balances on a given date.

This new functionality appears to work well within the current plan year.  Curious to see what would happen if I entered a date in the next year, I tried 01-Jan-2010.  I immediately received an error indicating a problem with a user defined function in the accrual formula "PTO_PAYROLL_CALCULATION". We'll have to take a closer look at this to see what is happening.  I would fully expect someone to try a date far into the future, so we'll need a more graceful ending than a framework error page.

Even with that small issue, the new Entitlement Balances functionality looks like a useful addition to both Employee and Manager Self Service.

Comment (2)

There has been a lot of back and forth between our DBA's and Oracle Support overnight, but so far, we are not much closer to a resolution.  Our initial problem was escalated to development and a bug was logged ( Bug 8553240 ). After our OWC session with development, Oracle sent us a file to place in the Class Path of our test instance.  The idea is that the supplied file will create extra debug statements to help track down the issue.  Unfortunately, the supplied file has not so far generated any information.

Luckily, our DBA's have been working closely with our internal Java developers and have passed some suggestions back to Oracle.  We are now waiting for Oracle Development to supply a new Java class file incorporating some of those suggestions.

Comment (0)

Testing the Appraisals functionality in 12.1 started out smoothly.  I selected the appraisal template, updated competencies, entered performance ratings and goals.  Then I clicked the submit button and it all stopped. I received the standard framework error  "You have encountered an unexpected error. Please contact the System Administrator for assistance. "

A few more tries with a different responsibility, different user, turned off personalizations, etc. Same error on submit.  I have an SR open with Oracle and conducted an OWC session with development today. This is the first "Show Stopper" (for Appraisals)  as far as my testing in 12.1 is concerned. The frustrating part is that the appraisal itself appears to work fine, it might be workflow related. 

I'm hoping for a quick resolution now that Oracle Development is involved and I'll let you know more as the SR progresses. 

Comment (1)

 I completed testing of the OTL functionality to import timecard data in a .csv format without any issues. I do need to make a note that a user helping out with testing this feature experienced intermittent issues.  The issues seemed to center around "expenditure type class combinations".  I need to do some additional investigating since I didn't run into these errors.   From the screen prints below, you can see one of my test imports into OTL.  I was able to save and submit this timecard without any problems.

 Recent Timecards screen with "Upload Timecard" button.

 Select the time period and browse to the location of the .csv file.

 

The .csv file is loaded. 

 

 

Comment (3)

After a small workflow related issue was resolved, I was able to complete the majority of OTL testing with no additional issues to report.  The one function left to test is the timecard import from a .csv file.  Although the import functionality is not rolled out to all users at KBACE, I am told it does come in handy for our back office people who often work multiple projects in the same week.  I'll test that early next week and hopefully be able to report that import works as expected.

 

Comment (1)

My OTL testing is nearly complete.  The only real issue encountered so far was a problem with workflow.  The test script I was executing required that the approver reject the timecard, sending it back for correction. Logged in as the approver, I rejected the timecard and then logged in as the submitter.  Unfortunately, when I logged in as the submitter, the timecard was still showing a status of "Submitted".  I waited a few minutes to be sure the background process had time to run, and checked again. The timecard remained in "Submitted" status, never showing the "Rejected" status I was expecting.

I logged back in as the approver and was able to see that an error had occured and that "Applsys.Wf_deferred_r" had errors.  I sent the error message over to our technical side and they were able to track down the issue, noted in Metalink document 577875.1.  I was then able to retest and everything worked as expected.

Running through OTL test scripts is not exactly exciting work, but the error showed why it is important to have good test scripts.  The submit and approve steps worked fine.  It was only when testing a submit/reject scenario that the issue with workflow appeared.  Take the time to test every possible combination of steps that might occur.  It is the only way you can be (mostly) sure of no big surprises once the applications goes into production.

Comment (0)

Our implementation of Employee Self Service is fairly straight forward.  Basic personal information, work information, payslips, online W2, W4 and a form to update skills for our KBACE resume.

First, what didn't work.  Payslips.  The current phase of our testing is using a scrambled instance.  Clicking on Payslips brings up a form that says "There are no Payslips for the pay period you can view. Please contact the System Administrator or Payroll Manager."  Payslips will have to be tested at a later date.

The link to update my KBACE resume skills also did not work.  The error message was a bit more ominous "Forbidden - You do not have permission to access this document".  It looks like maybe a low level permissions issue with the database or file system.  Probably a quick fix for one of our DBAs.

 The Online W2 was also not available for the current year, but selecting the W4 brought me to the Federal W-4 and allowed me to update my filing status.  I also had a link to my state withholding pdf which rendered correctly.

 Personal information , basic details, is view only.  I was able to make changes to phone numbers, address, and emergency contact information. 

So the bulk of the functions worked as expected.  And with the exception of the skills update, no errors to report for 12.1

Comment (1)

In version 12.1, Oracle made some changes to the user interface.  One of those changes was the merge of the "Time Entry -> Recent Timecards" screen with the "Timecard Search" screen.

The old version of the OTL menu appears below:

The old version of the screen for Recent Timecards (Navigation Time and Expenses -> Time Entry ) appears below.  Note the sub tabs labeled Time Entry and another for Timecard Search.

In 12.1, we now get a slightly modified menu with "Time Entry" and "Timecard Search" replaced by "Recent Timecards":

Selecting Recent Timecards brings us to the new merged screen.  Notice that the sub tabs for "Time Entry" and "Timecard Search" have been replaced by the "Recent Timecards" sub tab.

The merged screens are meant to provide for a cleaner user interface. Although you don't really save any time in navigation, it appears to be one of those small changes that slowly make for a more mature product.

Comment (0)