This discussion is archived
6 Replies Latest reply: Jun 26, 2013 12:54 AM by Bigman.Wang RSS

CALL TO PROCESS ORDER OE_ORDER_PUB.process_order

kmacpher67 Newbie
Currently Being Moderated
Using the SOAP ISG WSDL interface for API integration.

Product     Oracle E-Business Suite Integrated SOA Gateway
Product Version     12.1.3.5 (special ISG patch too)
Operating System     Linux x86-64

* Trying to create, update, modify existing orders, just starting with a simple test case of create an order.
* works fine from PLSQL direct call
* dies via SOAP call      <X_RETURN_STATUS>U</X_RETURN_STATUS>

How does one figure out the problem for this message?

Few things:

#1. How do we get this message Unexpected Error: FND_API.G_RET_STS_UNEXP_ERROR to show why?
#2. In our PLSQL we have oe_debug_pub.debug_on; & oe_debug_pub.initialize; How do we get the default WSDL package to do this?
#3. The response code from plsql comes back empty errorCode : null & errorTrace: null (is this supposed to be that way? )

Thu Sep 20 16:21:49 GMT-05:00 2012 : oracle.apps.fnd.soa.provider.util.AuditUtil.auditResponse : msgId : 10214
Thu Sep 20 16:21:49 GMT-05:00 2012 : oracle.apps.fnd.soa.provider.util.AuditUtil.auditResponse : operationName : PROCESS_ORDER
Thu Sep 20 16:21:49 GMT-05:00 2012 : oracle.apps.fnd.soa.provider.util.AuditUtil.auditResponse : responseDate : Thu Sep 20 16:21:49 GMT-05:00 2012
Thu Sep 20 16:21:49 GMT-05:00 2012 : oracle.apps.fnd.soa.provider.util.AuditUtil.auditResponse : errorCode : null
Thu Sep 20 16:21:49 GMT-05:00 2012 : oracle.apps.fnd.soa.provider.util.AuditUtil.auditResponse : errorTrace: null
Thu Sep 20 16:21:49 GMT-05:00 2012 : oracle.apps.fnd.soa.provider.SOAProvider.processMessage : Returning response for message received at Thu Sep 20 16:21:47 GMT-05:00 2012
  • 1. Re: CALL TO PROCESS ORDER OE_ORDER_PUB.process_order
    kmacpher67 Newbie
    Currently Being Moderated
    Updated the default ISG PLSQL package "ISG_OE_ORDER_PUB_PROCESS_ORDER.sql" with the following:

    OE_DEBUG_PUB.G_FILE := '';
    oe_debug_pub.debug_on;
    oe_debug_pub.initialize;

    FOR i IN 1 .. x_msg_count
    LOOP
    Oe_Msg_Pub.get( p_msg_index => i
    , p_encoded => Fnd_Api.G_FALSE
    , p_data => l_msg_data
    , p_msg_index_out => l_msg_index_out);
    x_msg_data := x_msg_data ||' '||l_msg_data;
    END LOOP;

    then xml response is:

    <X_MSG_DATA>ORA-06502: PL/SQL: numeric or value error: NULL index table key value in Package OE_Delayed_Requests_PVT Procedure LOGREQUEST User-Defined Exception in Package OE_Header_Util Procedure Pre_Write_Process</X_MSG_DATA>

    This is a weird message, again with the PLSQL API we have default record l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
    Do we have to do more details in SOAP XML request that are not normally required in the PLSQL API call because of this?
  • 2. Re: CALL TO PROCESS ORDER OE_ORDER_PUB.process_order
    kmacpher67 Newbie
    Currently Being Moderated
    How do we initialize a record table like we do in PLSQL when directly calling the API

    in PLSQL
    l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

    What's the equivalent when using SOAP request to build the default initialized <proc:P_HEADER_REC>
  • 3. Re: CALL TO PROCESS ORDER OE_ORDER_PUB.process_order
    kmacpher67 Newbie
    Currently Being Moderated
    Ok, so we modified the PACKAGE ISG_OE_ORDER_PUB_PROCESS_ORDER (edit the procedure and details)
    and edit the error out lines and put some debugging into the mapping function.

    Turns out the WSDL XSD validation is very strict, like the ORDER of the ELEMENTS must be exact or the SOA server craps out with mercurial messages.

    <faultcode>SOAP-ENV:Server</faultcode>
    <faultstring>FND_SOA_SERVICE_EXECUTION_ERR:oracle.apps.fnd.soa.util.SOAException: ServiceExecutionError: Error while executing the service Exception returned from JCA Service Runtime. Exception returned from JCA Service Runtime. null
         :Please see service monitor logs for full error trace</faultstring>

    Bottom line, get the ORDER of the ELEMENTS EXACTLY as the service is expecting or it doesn't map them into the PLSQL procedure. It's very brittle XML parser, maybe the coders uses an index to sequence the element list instead of name value lookup. It has to be ALL CAPS case sensitive too.

    We're using SOAPUI and 3rd party EAI WSDL Connection adapter so this is not Oracle middleware SOA Suite or 11 BPEL engine stuff, maybe it's easier if you utilize those native tools. I doubt it, but maybe.

    ken

    PS: make sure you use the
    <proc:InputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    ... set all the semi-important not used by you crappy elements to nil ...
    <proc:FIRST_ACK_CODE xsi:nil="true" />
    <proc:FIRST_ACK_DATE xsi:nil="true" />
    <proc:LAST_ACK_CODE xsi:nil="true" />
  • 4. Re: CALL TO PROCESS ORDER OE_ORDER_PUB.process_order
    712757 Newbie
    Currently Being Moderated
    Are you able to call the service successfully. I am struggling on this from last one week. Tried many different values for the input however it has standard reply status U ( unexpected error).

    If resolved can you please provide sample XM input message which worked for you.
  • 5. Re: CALL TO PROCESS ORDER OE_ORDER_PUB.process_order
    1009613 Newbie
    Currently Being Moderated
    Is this resolved? I have the same issue. Any ideas? I am trying "Delete Order" and I am passing all the parameters
  • 6. Re: CALL TO PROCESS ORDER OE_ORDER_PUB.process_order
    Bigman.Wang Newbie
    Currently Being Moderated

    hi, I get this error FND_SOA_SERVICE_EXECUTION_ERR  too.

     

    is yours solved?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points