none
biztalk oracle adapter error RRS feed

  • Question

  • Hi, Im getting the below error while invoking a method in oracle procedure AR_RECEIPT_API_PUB.
    Can anyone please help. I have cross checked the values multiple times (it works fine when executed with same values from toad)
    Any help would be greatly appreciated...we are stuck with this problem for 3 days now!
    Thanks
    Sathish

    Sample xml message:

    <ns0:CREATE_AND_APPLY xmlns:ns0="http://schemas.microsoft.com/[OracleApps://ACC.world/APPS/Procedures/AR_RECEIPT_API_PUB]"><ns0Stick out tongue_API_VERSION>1.0</ns0Stick out tongue_API_VERSION><ns0Stick out tongue_INIT_MSG_LIST>Fnd_Api.g_true</ns0Stick out tongue_INIT_MSG_LIST><ns0Stick out tongue_COMMIT>Fnd_Api.g_true</ns0Stick out tongue_COMMIT><ns0Stick out tongue_VALIDATION_LEVEL>100</ns0Stick out tongue_VALIDATION_LEVEL><ns0Stick out tongue_CURRENCY_CODE>INR</ns0Stick out tongue_CURRENCY_CODE><ns0Stick out tongue_AMOUNT>134.00</ns0Stick out tongue_AMOUNT><ns0Stick out tongue_RECEIPT_NUMBER>AXARCP03</ns0Stick out tongue_RECEIPT_NUMBER><ns0Stick out tongue_RECEIPT_DATE>2007-11-10T00:00:00</ns0Stick out tongue_RECEIPT_DATE><ns0Stick out tongue_GL_DATE>2007-11-10T00:00:00</ns0Stick out tongue_GL_DATE><ns0Stick out tongue_CUSTOMER_NUMBER>4182</ns0Stick out tongue_CUSTOMER_NUMBER><ns0Stick out tongue_CUSTOMER_RECEIPT_REFERENCE>AXARCP03</ns0Stick out tongue_CUSTOMER_RECEIPT_REFERENCE><ns0Stick out tongue_RECEIPT_METHOD_NAME>HSBC(FBL)</ns0Stick out tongue_RECEIPT_METHOD_NAME><ns0Stick out tongue_TRX_NUMBER>1117</ns0Stick out tongue_TRX_NUMBER><ns0Stick out tongue_AMOUNT_APPLIED>134.00</ns0Stick out tongue_AMOUNT_APPLIED><ns0Stick out tongue_APPLY_DATE>2007-11-10T00:00:00</ns0Stick out tongue_APPLY_DATE><ns0Stick out tongue_APPLY_GL_DATE>2007-11-10T00:00:00</ns0Stick out tongue_APPLY_GL_DATE><ns0:APP_COMMENTS>1209</ns0:APP_COMMENTS></ns0:CREATE_AND_APPLY>


    ERROR LOG


    Error transmitting message: <SOAP-ENV:Fault xmlnsTongue TiedOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><faultcode>SOAP-ENVTongue Tiederver</faultcode><faultstring>Request ID: Unknown Exception Type: System defined exception Exception Info: Exception occurred: Source: OracleApps Error Code: 6550 (0x1996) Cause: OracleApps://exception=DBException (Unique ID &lt;none&gtWink HY000 : [Oracle][ODBC][Ora]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY' ORA-06550: line 1, column 7 Exception data: struct DBException = WideString StatusCode = "HY000" WideString Description = "[Oracle][ODBC][Ora]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY' ORA-06550: line 1, column 7"</faultstring><detail><exposedBig SmileBException xmlns:exposed="http://schemas.microsoft.com"><exposedTongue TiedtatusCode>HY000</exposedTongue TiedtatusCode><exposedBig Smileescription>[Oracle][ODBC][Ora]ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY' ORA-06550: line 1, column 7</exposedBig Smileescription></exposedBig SmileBException></detail></SOAP-ENV:Fault> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
    Monday, December 8, 2008 12:02 PM

All replies

  •  

    Hi ,

     

    This seems to be a purly Oracle PL-SQL error. ORA-06550 is error points to the location in the PL/SQL where the syntax error occurred. Please go and check your procedure and try to compile it separately. 

     

    -Sanjay

    Tuesday, December 9, 2008 5:21 AM
  • Hi Sanjay,
    Thanks for the repsonse. This is a system procedure which comes with standard oracle apps packaged which is compiled and valid. Actually we are getting this error for all the procedures under this api  AR_RECEIPT_API_PUB
    Besides, if the procedure is executed from TOAD, it works just fine.

    Regards
    satheesh.s

    Tuesday, December 9, 2008 6:22 AM
  • Hi Satheesh,

     

    I have some doubt on following nodes in your Request.XML. All other except these two has some relavent values. I think these two values should be BOOL and the application which is generating this request XML is somehow not able to resolve "Fnd_Api.g_true". Try hard coding these values to BOOL.

     

    <ns0Stick out tongue_INIT_MSG_LIST>Fnd_Api.g_true</ns0Stick out tongue_INIT_MSG_LIST>
      <ns0Stick out tongue_COMMIT>Fnd_Api.g_true</ns0Stick out tongue_COMMIT>
     
     
    --Sanjay
     
     
     
    Wednesday, December 10, 2008 4:56 AM
  • Hi,

     

    I noticed that you are trying to pass a constant value 'FND_API.G_TRUE' in the XML that you are creating. Instead, you will probably need to use the actual value of the constant (which is 'T' in this case). Otherwise, the type and the value don't match at runtime.

     

    Please update the thread status in case it works. Also, can you confirm which version of the adapter pack are you using?

     

    Thanks

     

    Wednesday, December 10, 2008 5:02 AM
  •  

    Hi sanjay/sai,
    Thanks for the response.I did try the values with bool. Have attached the xml and the code used to run the stored proc from sql plus. It works fine with same values from sql plus.
    I suspect this to be a problem with setting application context. If i remove all the nodes from xml and send it with just the namespace i get the same error.However if there is a data problem, it shows the exact data error, on fixing that, it goes back to show the same wrongnumber or type of arguments.
    Im using adapter version 1.0   Microsoft BizTalk Adapter for Oracle(r) E-Business Suite

     

    Will also start a new thread on help for setting application context since im asking 2 questions here

    Thanks

    satheesh

     

    XML FILE

    Code Snippet
    - <ns0:CREATE_AND_APPLY xmlns:ns0="http://schemas.microsoft.com/[OracleApps://ACC.world/APPS/Procedures/AR_RECEIPT_API_PUB]">
      <ns0:P_API_VERSION>1.0</< FONT>ns0:P_API_VERSION>
      <ns0:P_INIT_MSG_LIST>T</< FONT>ns0:P_INIT_MSG_LIST>
      <ns0:P_COMMIT>F</< FONT>ns0:P_COMMIT>
      <ns0:P_VALIDATION_LEVEL>100</< FONT>ns0:P_VALIDATION_LEVEL>
      <ns0:P_CURRENCY_CODE>INR</< FONT>ns0:P_CURRENCY_CODE>
      <ns0:P_AMOUNT>134.00</< FONT>ns0:P_AMOUNT>
      <ns0:P_RECEIPT_NUMBER>AXARCP03</< FONT>ns0:P_RECEIPT_NUMBER>
      <ns0:P_RECEIPT_DATE>2008-12-05T12:12:18</< FONT>ns0:P_RECEIPT_DATE>
      <ns0:P_GL_DATE>2008-12-05T12:12:18</< FONT>ns0:P_GL_DATE>
      <ns0:P_CUSTOMER_NUMBER>4182</< FONT>ns0:P_CUSTOMER_NUMBER>
      <ns0:P_CUSTOMER_RECEIPT_REFERENCE>AXARCP03</< FONT>ns0:P_CUSTOMER_RECEIPT_REFERENCE>
      <ns0:P_RECEIPT_METHOD_NAME>HSBC(FBL)</< FONT>ns0:P_RECEIPT_METHOD_NAME>
      <ns0:P_TRX_NUMBER>1117</< FONT>ns0:P_TRX_NUMBER>
      <ns0:P_AMOUNT_APPLIED>134.00</< FONT>ns0:P_AMOUNT_APPLIED>
      <ns0:P_APPLY_DATE>2008-12-05T12:12:18</< FONT>ns0:P_APPLY_DATE>
      <ns0:P_APPLY_GL_DATE>2008-12-05T12:12:18</< FONT>ns0:P_APPLY_GL_DATE>
      <ns0:APP_COMMENTS>1209</< FONT>ns0:APP_COMMENTS>
      </< FONT>ns0:CREATE_AND_APPLY>

     

     

    Below is the code when executed from sql plus and it works fine

     

    Code Snippet

    declare

     v_return_status         VARCHAR2(10);
            v_msg_count       NUMBER;
            v_msg_data       VARCHAR2(1000);
     v_cr_id                 NUMBER; 
    l_out_index NUMBER;

    begin  

    Fnd_Global.apps_initialize(1153,50279,222); 


    ar_receipt_api_pub.create_and_apply (
             p_api_version                  => 1.0
            ,p_init_msg_list                => 'T'
            ,p_commit                       => 'F'
            ,p_validation_level             => 100
            ,x_return_status                => v_return_status
            ,x_msg_count                    => v_msg_count
            ,x_msg_data                     => v_msg_data
            ,p_currency_code                => 'INR'
            ,p_amount                       => 1000
            ,p_receipt_number               => 'AXARCP06'
            ,p_receipt_date                 => SYSDATE
            ,p_cr_id                        => v_cr_id
            ,p_receipt_method_name          => 'HSBC(FBL)'
            ,p_customer_number              => '1209'
            ,app_comments                     => '1209'
            ,p_customer_receipt_reference   => 'AXARCP02'
          --  ,p_remittance_bank_account_id   => 10144
            ,p_gl_date                          => '30-NOV-2008'
              , p_trx_number                     => '10170'
              , p_amount_applied                => 1000
          );

    dbms_output.put_line('Return Status ' || v_return_status);
    dbms_output.put_line('Message Count ' || v_msg_count);
    --dbms_output.put_line('Message Data ' || v_msg_data);
    dbms_output.put_line('Receipt id ' || v_cr_id);

    for i IN 1 .. v_msg_count LOOP
        FND_MSG_PUB.get(p_msg_index     => i,
                        p_encoded       => 'F',
                        p_data          =>  v_msg_data,
                        P_MSG_INDEX_OUT => l_out_index);

        DBMS_OUTPUT.PUT_LINE('Message Text ' ||  v_msg_data);

      END LOOP;


    End;
    /

     

     

    ERROR MESSAGE

     

    Code Snippet

    Error transmitting message: <SOAP-ENV:Fault xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"><faultcode>SOAP-ENV:Server</faultcode><faultstring>Request ID: Unknown

    Exception Type: System defined exception

    Exception Info:

    Exception occurred:

    Source: OracleApps

    Error Code: 6550 (0x1996)

    Cause: OracleApps://exception=DBException (Unique ID &lt;none&gt;)

    HY000 : [Oracle][ODBC][Ora]ORA-06550: line 1, column 7:

    PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY'

    ORA-06550: line 1, column 7:

    PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY'

    ORA-06550: line 1, column 7

    Exception data:

    struct DBException =

    WideString StatusCode = "HY000"

    WideString Description = "[Oracle][ODBC][Ora]ORA-06550: line 1, column 7:

    PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY'

    ORA-06550: line 1, column 7:

    PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY'

    ORA-06550: line 1, column 7"</faultstring><detail><exposed:DBException xmlns:exposed="http://schemas.microsoft.com"><exposed:StatusCode>HY000</exposed:StatusCode><exposed:Description>[Oracle][ODBC][Ora]ORA-06550: line 1, column 7:

    PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY'

    ORA-06550: line 1, column 7:

    PLS-00306: wrong number or types of arguments in call to 'CREATE_AND_APPLY'

    ORA-06550: line 1, column 7</exposed:Description></exposed:DBException></detail></SOAP-ENV:Fault>

    For more information, see Help and Support Center at

     

     

    Wednesday, December 10, 2008 7:09 AM