none
Mapping EBS Adapter Function Parameters to Oracle Function Parameters RRS feed

  • Question

  • I am trying to use the EBS adapter to query data from an EBS instance, and I am having trouble mapping the parameters in the adapter to the parameters in oracle.

    When I use the "Add Adapter Service Reference" wizard in the Visual Studio, the BizTalk Adapter for Oracle EBS shows me a list of the exposed functions available to me. From here I went to \Applications\Purchasing\Concurrent Programs\ and found the "Open Purchase Orders Report (by Cost Center)." This report has the short name of POXPOPAR in Oracle.

    Now, the function for this report inside the adapter has the following signature:
    public long POXPOPAR(string Description, string StartTime) { }
    So this takes 2 parameters, description and start time.

    The problem here is, that going into Oracle EBS, and looking at concurrent programs, we can lookup the report POXPOPAR and see that the parameters for the report are listed here. There are 7 parameters including Companys From, Companys To, Cost Centers From, Cost Centers To, and so on.

    So how do we provide these 7 parameters, if the adapter only takes the 2 parameters for description and start time?

    If anyone can help me figure out how to map these mismatching parameters, I would really appreciate the help. Thanks in advance!
    Monday, July 21, 2008 6:15 PM

Answers

  • Hi!

     

    I'm afraid the approach I mentioned in my earlier post might not work for you, as the adapter tries to verify the parameters it receives from the user against the metadata for the concurrent program it has. Till you get the fix, you can use the following workaround:

     

    1. Using add adapter service reference, under the root node, create proxy for ExecuteNonQuery operation.

    2. Create client

    GenericOperationClient myClient = new GenericOperationClient();

     

    3. Submit your concurrent program as follows:

     

    DataSet[] dataset = new DataSet[1];

    string query = "DECLARE \n ret number; \n BEGIN\n fnd_global.apps_initialize(1318, 50559, 222); \n ret := apps.fnd_request.submit_request ('AR', 'RACUST', '', '', FALSE, 'N'); open :REFC for select ret from dual; \n END;";

    int abc = myClient.ExecuteNonQuery(query, new string[] { "REFC" }, out datasets);

    string requestId= datasets[0].Tables[0].Rows[0][0].ToString();

     

    In the above example, 'N' in submit_request corresponds to arg1. in addition to the first 5 parameters, submit_request takes arg1 till arg100, all optional parameters. Example:

     

    apps.fnd_request.submit_request ('AR', 'RACUST', '', '', FALSE, 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL);

     

    has all 100 arguments specified (all but 1 of them are set to null).

     

    We are working to fix the issue, so that the adapter exposes concurrent programs with the right parameters in the right order, and also to expose a generic submit request operation.

     

    Thanks,

    Manas

    Wednesday, July 23, 2008 5:27 AM

All replies

  • Hi!

    What you have mentioned here is a known issue. The reason you're seeing this is 'coz metadata that is fetched by the adapter is incomplete (sometimes metadata for all parameters in the backend is not present). We're working to fix this in the next CTP. We'll also expose the generic submit_request API, in addition to get_status, set_options etc.

    For now, as a workaround, you can call the concurrent program using the WCF channel model (where you provide the XML to the adapter), and in the XML, populate all the parameters. So, your XML will look like this:

     

    <ARTALOGR xmlns="http://schemas.microsoft.com/OracleEBS/2008/05/ConcurrentPrograms/AR123">

    <Description>HY</Description>

    <StartTime></StartTime>

    <ParamA>123</ParamA>

    <ParamB>123</ParamB>

    <ParamC>123</ParamC>

    .

    .

    .

    </ARTALOGR>

     

    When the adapter makes the submit_request call, the first parameter after the <StartTime> will be mapped to the first custom parameter in the submit_request, the second one to the second custom parameter in submit_request and so on.

    Please provide your feedback on exposing a generic submit_request operation, to handle cases where the metadata fetched from Oracle backend is incomplete.

     

    Thanks,

    Manas.

    Tuesday, July 22, 2008 5:08 AM
  • Thanks for the reply!

    This definitely helps me with the parameters.

    This does raise other questions though. How do I know if I'm using the channel model, service model, or something else?
    Currently, the
    "Add Adapter Service Reference" wizard in the Visual Studio generates all the code for me.

    Is there any way you can send me some code examples of using EBS with the channel model? The current CTP only has OracleDB examples.

    Thanks again.

    Tuesday, July 22, 2008 4:28 PM
  • Hi!

     

    I'm afraid the approach I mentioned in my earlier post might not work for you, as the adapter tries to verify the parameters it receives from the user against the metadata for the concurrent program it has. Till you get the fix, you can use the following workaround:

     

    1. Using add adapter service reference, under the root node, create proxy for ExecuteNonQuery operation.

    2. Create client

    GenericOperationClient myClient = new GenericOperationClient();

     

    3. Submit your concurrent program as follows:

     

    DataSet[] dataset = new DataSet[1];

    string query = "DECLARE \n ret number; \n BEGIN\n fnd_global.apps_initialize(1318, 50559, 222); \n ret := apps.fnd_request.submit_request ('AR', 'RACUST', '', '', FALSE, 'N'); open :REFC for select ret from dual; \n END;";

    int abc = myClient.ExecuteNonQuery(query, new string[] { "REFC" }, out datasets);

    string requestId= datasets[0].Tables[0].Rows[0][0].ToString();

     

    In the above example, 'N' in submit_request corresponds to arg1. in addition to the first 5 parameters, submit_request takes arg1 till arg100, all optional parameters. Example:

     

    apps.fnd_request.submit_request ('AR', 'RACUST', '', '', FALSE, 'N', NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,NULL, NULL, NULL, NULL);

     

    has all 100 arguments specified (all but 1 of them are set to null).

     

    We are working to fix the issue, so that the adapter exposes concurrent programs with the right parameters in the right order, and also to expose a generic submit request operation.

     

    Thanks,

    Manas

    Wednesday, July 23, 2008 5:27 AM