Edit

Share via

Run an insert operation on an interface table in Oracle E-Business Suite using the WCF channel model

  • Article

The Oracle E-Business adapter discovers a set of Insert, Select, Update, and Delete operations on Oracle E-Business Suite interface tables. By using these operations, you can perform simple Insert, Select, Update, and Delete statements qualified by a Where clause on a target interface table. This topic provides instructions on how to perform an Insert operation on an interface table using the WCF channel model.

For more information on how the adapter supports these operations, see Operations on Interface Tables and Interface Views. For more information about how to perform operations on Oracle E-Business Suite using the WCF Channel model, see Overview of the WCF channel model with the Oracle E-Business Suite adapter.

About the Examples Used in this Topic

The example in this topic performs operations on the MS_SAMPLE_EMPLOYEE interface table. The table is created by running the script provided with the samples. For more information about samples, see Samples for the Oracle EBS adapter. A sample, InsertOperation, which is based on this topic, is also provided with the Oracle E-Business adapter samples.

The Insert Message

To perform operations on the Oracle E-Business Suite using the WCF channel model, you must have the request message specific to the operation. The request message to perform an Insert operation on the MS_SAMPLE_EMPLOYEE interface table resembles the following:

<Insert xmlns="http://schemas.microsoft.com/OracleEBS/2008/05/InterfaceTables/FND/APPS/MS_SAMPLE_EMPLOYEE">  
  <RECORDSET>  
    <InsertRecord xmlns="http://schemas.microsoft.com/OracleEBS/2008/05/TableViewRecord/APPS/MS_SAMPLE_EMPLOYEE">  
      <EMP_NO>10050</EMP_NO>  
      <NAME>John Smith</NAME>  
      <DESIGNATION>Manager</DESIGNATION>  
      <SALARY>500000</SALARY>  
      <JOIN_DATE>1999-05-31</JOIN_DATE>  
    </InsertRecord>  
  </RECORDSET>  
</Insert>

This request message inserts a record with following details:

Employee Number = 10050  
Name = Tom Smith  
Designation = Manager  
Salary = 500000

You must copy the message to a file, e.g. InsertRequest.xml. This file is used in this example to send the request message to Oracle E-Business Suite using the Oracle E-Business adapter. For more information about the message schema for operations on table, see Message Schemas for Insert, Update, Delete, and Select Operations.

Creating a WCF Channel Application

This section provides instructions on how to create a WCF channel application to perform an Insert operation on the MS_SAMPLE_EMPLOYEE interface table.

To create a WCF channel application for inserting records into the table

  1. Create a Visual C# project in Visual Studio. For this topic, create a console application.

  2. In the Solution Explorer, add reference to Microsoft.Adapters.OracleEBS, Microsoft.ServiceModel.Channels, System.ServiceModel, and System.Runtime.Serialization.

  3. Open the Program.cs file and add the following namespaces:

    • Microsoft.Adapters.OracleEBS

    • Microsoft.ServiceModel.Channels

    • System.ServiceModel

    • System.ServiceModel.Channels

    • System.Xml

  4. Create the binding and endpoint.

    OracleEBSBinding binding = new OracleEBSBinding();  
EndpointAddress address = new EndpointAddress("oracleebs://ebs_instance_name");

  5. Because you are performing an operation on an interface table, you must set the application context. In this example, to set the application context, you specify the OracleUserName, OraclePassword, and OracleEBSResponsibilityName binding properties. For more information about application context, see Set application context.

    binding.OracleUserName = "myOracleEBSUserName";  
binding.OraclePassword = "myOracleEBSPassword";  
binding.OracleEBSResponsibilityName = "myOracleEBSResponsibility";

  6. Create and open the channel factory. This application sends request message to Oracle E-Business Suite and receives a response, hence you must implement the IRequestChannel interface.

    ChannelFactory<IRequestChannel> factory = new ChannelFactory<IRequestChannel>(binding, address);  
factory.Credentials.UserName.UserName = "<Enter user name here>";  
factory.Credentials.UserName.Password = "<Enter password here>";  
factory.Open();

  7. Create and open the channel.

    IRequestChannel channel;  
try  
{  
   channel = factory.CreateChannel();  
   channel.Open();  
}  
catch (Exception ex)  
{  
   Console.WriteLine("Exception :" + ex.Message);  
   throw;  
}

  8. Create and send the request message.

    XmlReader readerIn;  
try  
{  
   readerIn = XmlReader.Create("InsertRequest.xml");  
   Console.WriteLine("Reader created");  
}  
catch (Exception ex)  
{  
   Console.WriteLine("Exception: " + ex.Message);  
   throw;  
}  
Message messageIn;  
Message messageOut;  
try  
{  
   messageIn = Message.CreateMessage(MessageVersion.Default, "InterfaceTables/Insert/FND/APPS/MS_SAMPLE_EMPLOYEE", readerIn);  
   messageOut = channel.Request(messageIn);  
}  
catch (Exception ex)  
{  
   Console.WriteLine("Exception: " + ex.Message);  
   throw;  
}

    While creating the request message, you must specify the message action that indicates the action that the adapter performs on the interface table. To perform an Insert operation on the MS_SAMPLE_EMPLOYEE table, the message action is InterfaceTables/Insert/FND/APPS/MS_SAMPLE_EMPLOYEE. For information about how you can determine the message action for various operations on tables, see Message Schemas for Insert, Update, Delete, and Select Operations.

  9. Get the response message.

    XmlReader readerOut = messageOut.GetReaderAtBodyContents();  
XmlDocument doc = new XmlDocument();  
doc.Load(readerOut);  
doc.Save("C:\\Response.xml");

  10. Close the message, channel, and channel factory.

    messageOut.Close();  
channel.Close();  
factory.Close();

  11. Build the project. After building the project, you must copy the request message, InsertRequest.xml, at the same location as your project executable. Typically, this location is \bin\Debug\ under your project directory.

  12. Run the application. The response message, Response.xml, is saved at the location you specified in the application. The response message contains the number or records inserted and resembles the following:

    <InsertResponse xmlns="http://schemas.microsoft.com/OracleEBS/2008/05/InterfaceTables/FND/APPS/MS_SAMPLE_EMPLOYEE">  
  <InsertResult>1</InsertResult>  
</InsertResponse>

    The value “1” denotes that a single record is inserted into the MS_SAMPLE_EMPLOYEE table.

See Also

Develop Oracle E-Business Suite applications using the WCF Channel Model