Projects:Openbravo POS Improvement of Synchronization/Functional Specification
Contents |
Overview
The goal of this project is to improve the integration between Openbravo POS and Openbravo ERP.
Purpose
The purpose of this project is to improve the integration between Openbravo ERP and Openbravo POS. The area that are going to be improved are customer data, taxes, payments and the user interface.
Scope
- Increase the data synchronized.
- Synchronization of all additional customer fields of Openbravo POS 2.20 like Location and Contact fields.
- Synchronization of the new tax system in Openbravo POS 2.20.
- Synchronization of payments must be improved. POS provides opportunity to sell on credit and by synchronization ERP does not know if the customer has already paid or not.
- Synchronization of product attributes.
- Synchronization of product inventory with attributes associated.
- Synchronization of product refunds.
- Improve synchronization processes.
- By importing orders in ERP, each order must be set by user to be completed and the date of a payment must be forwarded. All these steps must be executed automatically.
- Improve External Point of sales configuration.
- In ERP-POS configuration another field is needed to take care about a currency used in POS. In products price synchronization and in order amounts synchronization. In the current 2.20 version there is an issue associated: After synchronization of orders, if the currencies are different, the prices are wrong in ERP. 5158
- Improve the architecture and execution of the synchronization process.
- It should be possible to set synchronization to run automatically and without impacting the work on POS.
References
Design Considerations
Architecture
The present architecture is not much efficient. The SOAP protocol with the RPC messaging pattern is used to communicate.
Openbravo POS calls Openbravo ERP's Web Services and the called web service is immediately sent to POS. The diagram below presents how the synchronization of customers works. POS calls the suitable ERP's web service so in this case the web service regarding customers. In ERP a customer is called a business partner and can have a few data contacts and a few data locations. In easier way to understand a business partner can have a few addresses such as an address of a head office and an address of an IT department and of course a few phone numbers to contact with them. That's way 3 tables in the database are required to meet those requirements. In POS a customer only owns one address and basic contact's data. So the web service in ERP apart from basic data about a business partner gets only one address and one contact from the database and send them to POS. Everything is stored in a table Customer with all data.
This is the simplest solution and it works quite good in situations where not too much data is needed to synchronize. In the next release additional Web Services are required to be added. This is a good point to change the present architecture of Web Services. Changes are desired because each time when some new data needs to be synchronized a developer must add a suitable piece of code on both sides, so either in POS and in ERP. By a small amount of data this is not a big problem. However in case of extension of Web Services a lot of effort must be put into. A good solution is to use a middleware software such as Kettle provided by Pentaho. Pentaho Data Integration delivers powerful Extraction, Transformation and Loading (ETL) capabilities using an innovative, metadata-driven approach. Use of it would allow to create Web Services, get data directly from a database and store data directly in a database.
The diagram above presents a solution to synchronize ERP and POS using a middleware software. ERP creates similar Web Services like previous but the middleware component save the data directly in the database of POS. The other thing is that a POS does not have to call Web Services since that can be done automatically i.e. by time or an event such as closing a cash.
This kind of implementing would provide very simple way to create Web Services without coding too much and much more efficient than it works right now.
Web services current development
In the current Openbravo ERP development methodologies, web services are created from scratch using AXIS.
Web services source code is organized in /src/org/openbravo/erpCommon/ws, where are the web service implementation classes, data structure, classes and xSQL data access definition. All web service methods include a username and password parameter for authentication, and data access authorization.
Also a deploy.wsdd file is provided in /src with the web services description to allow AXIS to deploy them.
Web services proposal development
In the roadmap for Openbravo ERP, a new data layer will be developed with the ability to expose data as web services. This data layer may be used to develop the web services needed by the Openbravo POS Synchronization.
These new web services data layer may be of two different classes:
- Expose Openbravo ERP data based on the database tables and fields only with no possibility to execute complex SQL queries or expose complex data objects structures, but with the ability to create filters based on table fields.
- Expose Openbravo ERP data based on complex SQL queries and complex data objects.
Depending on the features available in the new web services for Openbravo POS the logic of the synchronization may be created in one layer or other layer.
Functional Requirements
User roles & profiles
- System administrators
- Typically these users have a high level of professional education, are computer literate and receive training in the product process. These users perform administrative tasks and maintenance of the system.
- Consultants
- This type of users have a high level of professional education, have a deep knowledge of the business processes. They need the tools to adapt and extend the integration data processes in an easy way without having to deal with the internal complexities of the applications.
Business process definition
User stories
- Story 1. Maintenance of integration processes
- The system administrator must be able to setup and configure the integration processes between Openbravo POS and Openbravo ERP.
- When new integration packages are developed, he must be able to deploy them without putting in risk the production environment.
- The system administrator must take care of the issues when data integration fails and have the tools to recognize the errors and to fix it
- Story 2. Adaptation of integration processes
- Consultants must be able to adapt and extend the integration processes
- Integration processes must be testable in a development environment.
- After a new integration package or modification has been created they must be able to package the integration developed and give it to the system administrator to deploy it in the production environment
Functional requirements based on business processes
General
| Requirement | Importance | Status | Comment |
|---|---|---|---|
| Use a middleware application to perform the integration of data for the current integration implementation | Must have. | Not started | This includes to use kettle for the integration logic, database connectivity and web services connectivity. |
| Implement the current web services needed in the integration using the new DAL | Must have | Not started | This includes the web services needed that are not an immediate image of the Openbravo ERP data layer, like the products list and the orders import. |
| Integration process execution on events | Nice to have | Not started | This includes the ability to launch the integration process based on events of the Openbravo POS application or Openbravo ERP application. For example to launch the upload orders process when the cash is closed in Openbravo ERP. |
Integration extension
| Requirement | Importance | Status | Comment |
|---|---|---|---|
| Integrate all customer data needed in POS | Must have. | Not started | This includes customer fields, location fields, and contact fields |
| Integrate all taxes data | Must have | Not started | This includes taxes fields, tax categories fields and customer tax categories fields. |
| Integrate all product attributes data | Must have | Not started | This includes the new product attributes feature. |
| Integrate all inventory data | Nice to have | Not started | |
| Integrate all refunds data | Nice to have | Not started | |
| Integrate all payment data | Nice to have | Not started |
Technical Requirements
Customers synchronization
In ERP a Business partner can have several locations and several contacts but in POS there is only one table for Business partners called CUSTOMERS and one Business partner in POS have one and only one location and one and only one contact. The ERP location used to synchronize POS should be the billing location and if there are more than one billing location choose the first one. The ERP contact used to synchronize should be a contact with the same location as the location selected for the Business partner.
ERP Web services (Option 1)
- BusinessPartner[] getBusinessPartners()
Get a list of business partner with the location and contact suitable for the POS. The BusinessPartner object has the following structure:
BusinessPartner(ID, SearchKey, Name, TaxID, BusinessPartnerCategory, Active, Description, Location 1st line, Location 2nd line, Postal code, City, Region, Country, Contact first name, Contact last name, Email, Phone, Alt Phone, Fax)
ERP Web services (Option 2)
- BusinessPartner[] getBusinessPartners()
Get a list of business partner. The BusinessPartner object has the following structure:
BusinessPartner(ID, SearchKey, Name, TaxID, BusinessPartnerCategory, Active, Description)
- Location[] getLocations(BusinessPartner ID, Filter)
Get a list of locations of the selected Business partner filtered by type in order to allow the synchronization process to select the location suitable for the POS. The Location object has the following structure:
Location(Location 1st line, Locatinone|on 2nd line, Postal code, City, Region, Country)
- Contact[] getContacts(BusinessPartner ID, Filter)
Get a list of contacts of the selected Business partner filtered by location to allow the synchronization process to select the location suitable for the POS. The Contact object has the following structure:
Contact(Contact first name, Contact last name, Email, Phone, Alt Phone, Fax)
POS Tables
- CUSTOMERS(ID, SEARCHKEY, TAXID, NAME, TAXCATEGORY, CARD, MAXDEBT, ADDRESS, ADDRESS2, POSTAL, CITY, REGION, COUNTRY, FIRSTNAME, LASTNAME, EMAIL, PHONE, PHONE2, FAX, NOTES, VISIBLE, CURDATE, CURDEBT)
Data transformation
Once selected the ERP location and contact to synchronize the fields are the same with the following exceptions:
The CARD field in POS is the value of the loyalty card that does not appear in the ERP, then it should not be synchronized.
The MAXDEBT, CURDATE, and CURDEBT fields in POS refer to the credit status of the customer and should be synchronized in the Payment data synchronization.
Taxes synchronization
ERP Web services
- TaxCategory[] getTaxCategories()
Get a list of taxes categories. The TaxCategory object has the following structure:
TaxCategory(ID, Name)
- BPTaxCategory[] getBPTaxCategories()
Get a list of Business partner tax categories. The BPTaxCategory object has the following structure:
BPTaxCategory(ID, Name)
- TaxRate[] getTaxes(Filter)
Get a list of tax rates filtered by sales type, tax country, valid date, ... The TaxRate object has the following structure:
TaxRate(ID, Name, TaxCategory_ID, BPTaxCategory_ID, ParentTaxRate_ID, Line_no, Cascade, Rate)
POS Tables
- TAXCATEGORIES(ID, NAME)
- TAXCUSTCATEGORIES(ID, NAME)
- TAXES(ID, NAME, CATEGORY, CUSTCATEGORY, PARENTID, RATE, RATECASCADE, RATEORDER)
Data transformation
The taxes structure in ERP and POS is exactly the same and no data transformation is needed, just copy the fields information from one structure to the other.
Payments Synchronization
ERP Web services
POS Tables
- PAYMENTS(ID, RECEIPT, PAYMENT, TOTAL)
Data transformation
Cash synchronization
Closing cash data is not synchronized between Openbravo ERP and Openbravo POS right now. It will require a new table in Openbravo ERP.
ERP Web services
- ClosingCash[](HOST, SEQUENCE, MONEY, DATESTART, DATEEND, PAYMENT, TOTAL)
POS Tables
- CLOSEDCASH(MONEY, HOST, HOSTSEQUENCE, DATESTART, DATEEND)
- PAYMENTS(ID, RECEIPT, PAYMENT, TOTAL)
- RECEIPTS(ID, MONEY, DATENEW, ATTRIBUTES)
Data transformation
All data is retrieved from a database on the POS's side and no data transformation is required. The result of the SQL statement must be just copied into the database on the ERP's side.