Integrate with Oracle EBS with WebService

Http://www.cnblogs.com/isline/archive/2010/04/15/1712428.html

I. Overview

Oracleebs is the ERP product of Oracle company, this product is very large, can carry on the powerful management function to each aspect of the enterprise, the general enterprise will use some of its module, according to the nature of the company, choose the module also will be different. For production-oriented enterprises, the procurement of modules will include products and prices and other management modules.

ERP is a large-scale system, can choose the enterprise of Oracle ERP, the size is generally not too small, so in it, in addition to the ERP product itself, there will generally be some other small proprietary business systems to support, some of these systems are in use before the ERP on-line, Can not simply use ERP products to replace them all. According to the actual situation, in order to achieve the synchronization of some information between each system and EBS, especially the product information, generally will be selected in the ERP system maintenance, and other systems can directly refer to the ERP system information, so as to ensure that the product information only a set, there will be no redundancy, there will be no ambiguity.

Our company in the previous interface with the ERP, the use of a lot of methods, the most common is directly from the ERP read information, and then directly into the business system, or by the business system initiated, directly to the ERP write information. For simple information, this approach is basic, but for more complex business logic, the risk is not underestimated, because based on the experience of using EBS, the design of EBS is very complex, the table structure and the application of the relationship is also complex, sometimes feel that modifying a table is enough, But in the real use, or after a period of time, only to find that the ERP data consistency has been destroyed, and sometimes even cause problems in the ERP system, and sometimes it is difficult to recover, so this is not advisable.

EBS itself provides a series of interface tables, so the process of writing data to EBS, the Oracle company is recommended to use the interface table, after the temporary data written to the interface table, Oracleebs products will have a corresponding interface program to implement the docking table description and Import production table, This will try to maintain the data integrity of the EBS. Of course, the operation of a database table is inherently, but it's a lot more secure.

The above mentioned generally refers to the application system and ERP system are in the same network segment of the company, in this case it is possible to use a database connection way to integrate, but for some applications are located in different geographical location, even in overseas, this way seems to be inadequate, Because requests originating from other datacenters are not directly accessible to the ERP database.

In fact, a lot of the integration requirements of the system abstraction, there is a great similarity, but if the individual manufacturers themselves to set standards, may have a greater impact on EBS. In this case, you might consider standardizing the EBS interface.

There are several issues to consider when standardizing the EBS interface

Ø Data Security

The interface can only open the necessary parts of the relevant system, and not allow more business data to open outward;

The relevant application system can only be authenticated by a legitimate means, and an unauthorized system cannot use the interface.

Ø consistency of data

EBS system structure is quite complex, in the data write, must pay attention to the integrity and consistency of the data, if there are multiple tables in the system redundancy, it is necessary to all the related tables all at the same time, if the table is not comprehensive, will cause EBS fatal error. In order to achieve this consistency, it is necessary to use EBS's own interface table system as much as possible and then handle the subsequent work by the EBS's own standard request.

Ø versatility of the system

This interface system needs to consider the current need to access the general support of several systems, that is, this interface system as far as possible with the business system-independent, through a simple configuration can be implemented to different systems support;

Another factor to consider is the third-party system that accesses EBS, some located at the corporate headquarters, which can be connected directly to the EBS database server, while some are located overseas and do not have the ability to directly connect to the EBS database, in order to achieve this commonality, consider using the webservice approach, That is, publish a public network of webservice, so that it and EBS database connection, so that you can achieve a different geographical location of the system connection.

Second, Architecture

The system is divided into two parts, one is the EBS and interface system of the enterprise, the second part is the third party manufacturer's self-built system.

In the Enterprise Headquarters system part, divided into two parts, part of the WS interface System, the second part of the original EBS system.

In the EBS section, it can be divided into formal table and interface table two parts.

The overall requirements are technically divided into two categories:

Ø Read-only EBS

WS reads data directly from the EBS Production table or interface table, because it does not involve write operations, so as long as the database structure is clear to obtain the required data;

Ø read/write EBS (requires interaction)

This part of the work is complex and requires write operations to EBS, because the complexity of EBS requires that the content be written to the interface table, which is handled by standard requests to import the interface table into the formal table.

In the process of importing data into a formal table, it is possible that a validation failure may occur, in which case the standard request will generally record the error information in the interface table or elsewhere, and if the third party needs this information, the interface table can be called to draw the corresponding conclusion. In short, third-party and EBS interactions can be combined in two ways, but the efficiency of the interaction may be relatively slow, that is, a certain delay in the middle.

The so-called standard request, is the EBS comes with some of the features, as to what it has standard requests, need to consult EBS related documents, some of them have, they do not, who can not provide.

In terms of technical architecture, the WebService interface is developed using Visual Studio 2008来 because WebService is independent of the specific programming language, so it can be used by multiple third-party vendors as long as the standard interface rules are used.

Third, WebService Interface Implementation

WS servers are implemented using WINDOWS2008+IIS, which enables third-party vendors to communicate and interact with EBS, ensuring that third-party vendors ' systems, whether at headquarters or overseas IDC, can communicate with EBS smoothly. In this part of the interface implementation, a number of sections are explained:

Ø Secure Communication

In the third-party and WS communication, the need to consider the security of communications, WS itself will use HTTPS to work.

Because there is no guarantee that all third-party systems can be used in the same way as domain validation, there are some more general methods of WS validation that need to be considered in data transfer.

In each new third-party vendors, the system will assign them a password string (32 bytes), this cipher string is unique to each vendor (System), and does not communicate with each other between different vendors, each time the data sent, with this cipher string sent together, so you can first determine the validity of the uploaded data, Similarly, the data will be used in this way when it is transmitted.

Ø Flexibility

WS design principle should be as far as possible to encapsulate the database table, so that the third party does not need to too much knowledge of the EBS table structure can work properly, but also to consider the extensibility of WS, so can not do too many interface functions, should try to use the schema of the way to describe, Each schema will correspond to specific business and operations, the schema extension is quite flexible, with the development of business and new third-party vendors to continuously improve and develop, to form a standard EBS interface Schema Library, and to form a corresponding description, you can select the appropriate schema to use.

So far, we've only explained the two aspects of implementation, security and flexibility, and we'll explain several other aspects in the next installment.

Ø Read Data

Reading data using specialized interface functions, and there is only one function, you can do several overloads to achieve different needs, more often by the schema parameter to distinguish the type of operation, the returned type is an XML file, and the third party will know how to disassemble the returned XML. XML can be a single table, but also the form of a multi-table recordset, the specific return format is entirely specified by the schema, before returning, consider the efficiency of the transfer, you need to compress the returned data, and then the form of a binary array to return

² Function Prototypes

Public byte[] GetData (string schemaname,string whereclause,string password)//check out the recordset for the specified condition

² Parameters

SchemaName

Specifies the mode name, which is described in the following sections for modes and modes of use

Depending on the specified pattern, the system will find the corresponding table and return the required fields

Whereclause

It returns records based on a query condition, which allows for complex query functionality, which returns a recordset that follows the specified schema

Password

A hash string that is assigned by the system to a third-party vendor (System), as a unique identifier, is checked by the server side to verify the legitimacy of the cipher string and cannot be manipulated if the string does not exist in the system.

The specific description and usage of the cipher string are described in the following sections.

² return Value

The return value is a byte array, instead of the direct return of XML, so that you can do more processing before transmission, such as compression and encryption, this design is not included in the encryption processing, compression using the standard compression algorithm.

After the third party receives the byte array, it uses the standard compression algorithm to unpack it, reverting it to an XML file

In the third-party client, there will be a corresponding schema to parse the XML, the specific use of which schema, by the client to grasp, and the server-side, information after the disassembly of how to use, also by the client to grasp.

If the operation fails, the returned error is returned in the form of an array of bytes, so the client first checks to see if the operation was successful, and if the acknowledgement does not fail, check the specific return.

Ø Write function

Write action refers to the client-initiated, write to the server related content, in this system, the content is written to the business-independent, all abstracted to the technical level to deal with.

² Function Prototypes

Public byte[] WriteData (string schemaname,byte[] content,string password)

² Parameters

SchemaName

Similar to the function of the read function.

content

The content to be written, generated at the client, first generates an XML file that corresponds to SchemaName, generates XML, and then compresses and encrypts the client, generating a binary byte array to upload to the server.

Password

Similar to the function of the read function.

² return Value

Like the read function, a byte array is returned, and the client first extracts and decrypts it before it can be used, although a very small amount of content will be larger after compression, but it still works in this way for a unified management approach.

Ø Data Transfer

Data is transmitted in two directions, that is, some are written to EBS from a third-party system, and some are returned from EBS read to a third-party system.

For one-way read (ebs-> third-party), there is no risk of data consistency, directly return, completely through the addition and configuration of the schema can achieve data security and effective control.

However, for another aspect of the transmission (third-party->ebs), there is a more sensitive problem, because the data structure of EBS is very complex, it is difficult to fully grasp its structure, so this direction of transmission, it is important to grasp a principle that can only be written to the EBS interface table, can not be directly written to the production table. This part of the content will be described in more detail in the schema section.

In the next installment, we will discuss the security of the system in detail in the future.

Iv. Security System

The above section also mentions the security of the data and some strategies, which will detail the safe data transfer.

Ø SSL system

This is a network layer of the solution, the practice is to configure the appropriate SSL certificate on IIS to implement an SSL site, the security certificate is VeriSign, can be purchased from the agency of the country.

SSL will increase the security of the system, but it will also have a little bit of impact on performance, but it will not become a system performance bottleneck.

It is also necessary to verify that all third-party vendors support this SSL method when writing the program, so the system can be used as an option, ready to be enabled, without compromising the overall architecture.

Ø compression and encryption

When the data in the client and server communication, if not using SSL, the information on the Internet can be intercepted and cracked, in order to solve this problem, the data needs to be pre-processed, and then transferred.

Compression is a mandatory option, using the standard ZIP compression algorithm, the client uses what tools to compress and decrypt by the client own grasp, do not provide specialized processing functions.

For the encryption algorithm, you can use the standard encryption algorithm, you can also use their own definition of simple encryption algorithm, you can also temporarily do not consider encryption, only with compression processing, this issue is not considered encryption algorithm.

Ø Password string (identification code)

Each third-party vendor (System) will be assigned a unique identification code, in order to enhance the strength of this identification code, using a 32-byte length of the cipher string, the password string itself does not have any meaning, only as a unique identifier, increase its length, that is, reduce the risk of misappropriation.

About the distribution of the cipher string, regardless of the program implementation, only by hand processing can be sent using the way mail.

In order to verify the password string, the server side and the client are required to store the cipher string at the same time, so that it can be verified. The server here refers to the WebService server, not the EBS server, because it is inconvenient to add too much custom content to EBS.

On the server side, the storage of the cipher string can be directly used by the XML file or flat file can be, this can be implemented at the time of the decision, in the file only need to simply store the following information:

² Cipher Strings

The string used for communication, 32 bytes long, hand-generated, random generation, no rules, as long as the guarantee can be properly issued to the relevant third-party manufacturers hands can.

² Manufacturer Name

For identification, this identity is not used in the system.

² Valid Starting Date

After this date is greater than this, the password string is considered to be legitimate, this field cannot be empty, and special cases can be handled on a very small date.

² Effective End Date

Less than this date, the password string is considered to be legal, this field cannot be empty, can be considered to be indefinite by a large date, the above two dates can be adjusted at any time to change the availability of the password.

² Creation Date

The date that this record was added.

² Creator (String description)

Who added this record.

² Last Modified Date

² Last Modified Person

When the server IIS started, in the Instance_startup type of function, plus special processing, all the cipher strings are read out, into the global variable (only read the password string one field, others are not read), in WebService getdata/ In the WriteData function, it is preferred to check if the incoming password string parameter is legal, if it is legal, and then proceed to the subsequent operation, if it is illegal, return the error "illegal use" directly.

The validation functions are as follows:

private bool Validpassword (string password)

After the client receives the headquarters about the password the mail, all operation must carry this password, if because the password fills the wrong, will cause a series of errors, needs the third party own grasp.

About password changes:

Password change on the server need to change the valid end date of the original password, and record the change person;

Add a new record at the same time and confirm that the end date is valid;

Finally, the new password is sent to the third party vendor.

Ø database table ACL

In principle, the client reads and writes the data in accordance with the agreed rules, but in order to prevent malicious reading and writing, the ACL is specifically set to control which tables can be read and written, and ACL files are standard XML files, including the following three properties:

² table Name (UPPERCASE)

² is readable (BOOL type)

² is writable (BOOL type)

Similarly, this information is read into memory when the system is started.

Ø Private Information

In the same interface table, there may be multiple vendors to operate at the same time, in order to ensure that the operation between the different vendors do not affect each other, for this need to find a suitable interface table in the appropriate field to store the vendor's password string.

In the following section, we will describe the schema, which is the basic unit of data communication.

V. XML Schema

Schema is the most important part in order to achieve more versatile interactions.

The schema here is the standard template XSD document, followed by all standard specifications.

The details of the XML Schema are not described here and can be explained on their official website.

When reading data and writing data, Schema,schema is used to implement a mapping of the original table.

There is a schema list on both the server side (WebService server) and the client, which stores all schemas on the server, but in the client, only the parts that are related to the business are stored and not all schemas are stored.

Ø Data Read

The schema file is used for the description of the data, but there is no dependency on the database itself, and it is now up to the schema to get an SQL statement to fetch the data from the database. In order to quickly and easily implement the schema and SQL statement conversion, you can write a special file, the file name is the same as the schema, the extension is. Select, which is a SQL statement that selects data that exists only on the server and the client does not know the existence of the file. In the Instance_startup function, all the relevant SQL is read all at once into memory, convenient for later use quickly, of course, if there are any changes in this file, you need to restart the IIS.

For the Where condition, can be used as a string directly behind the SQL, get a real SQL statement, SQL without the concept of parameters, splicing completed SQL, can be used directly in the database, in order to ensure the availability of SQL, The client must follow certain rules when writing the where condition, including matching relationships such as and/or, the use of parentheses/single quotes, etc., must be correct.

There are two scenarios for the generation of standard XML:

If oracle10g supports XML SQL, XML files that conform to the XSD standard can be generated directly;

If not, you can only return a dataset, and then refer to the schema format to generate an XML file manually.

After the XML is generated, the compression toolkit is called to compress it, generating a binary array and returning it directly.

If an error occurs during use, a code such as "Query Error" is returned, and the associated error code can be returned.

Before reading, you need to determine if this table is readable in the ACL list (you can skip this step if you are not sure, because SQL is not a client-side change).

Ø Data Write

The data written in the function has a byte array, first decompression and decryption, generate an XML file, this file with the schema, read into the system as a standard dataset, you can use the existing technology directly to write the content to the database, before writing, Be sure to check the ACL to see if the table can be written.

VI. EBS -Side related operations

All writes are written to the EBS interface table and are not written directly to the formal table, so after writing to the interface table, it is necessary to invoke the EBS standard request to write the interface table to the formal table.

On the interface table, different modules have different interface tables, and there are different ways of operation, there are different requests to implement, so it is necessary to consider the EBS interface table and standard request according to the actual interface situation, and to study the error message returned by the standard request and the meaning, there is the error returned storage location, Some write directly back to the interface table, and some are written to the standard error log table, which is determined at the time of operation.

Seven, Summary

The above way has been in my company to get real application, and has been for a number of business systems service, the use of good results, I hope you can help after reference.

Integrate with Oracle EBS with WebService