Views
Technical Specifications
From Wiki
Contents |
Overview
This documents describes the technical specifications to implement the bmSales integration.
The implementation is separated in 4 phases:
- Creation on configuring window.
- Development of the process to upload the data to Elondra's server.
- Development of the process to download the orders from Elondra's server.
- Implementation of the scheduling capabilities to both processes.
The development will be done as a module of the 2.50 version of Openbravo ERP:
- module name: bmSales integration
- java package: org.openbravo.connector.bmSales
- prefix: bmSales
Creation of configuring window
The window will be called Elondra Server configuration and will have four different tabs. In the header (bmsales_configuration table) there are defined the connection used for each organization. In the Users tab (bmsales_users table) there are defined the different users that can enter sales orders in bmSales in the selected configuration. For each configuration it is possible to define the shipping routes in the Shipping tab (bmsales_shipping_route table). And for each defined user it is possible to define its routes in the routes tab (bmsales_user_route table).
Column and fields relation (more might be added during the development of the processes):
bmsales_configuration table
| Column Name | Display Name | Type |
|---|---|---|
| bmsales_configuration_id | bmSales configuration | varchar2(32) |
| ad_client_id | client | varchar2(32) |
| ad_org_id | organization | varchar2(32) |
| isActive | active | char(1) |
| created | created | date |
| createdBy | created by | varchar2(32) |
| updated | updated | date |
| updatedBy | updated by | varchar2(32) |
| server_url | server url | nvarchar2(90) |
| service_alias | service alias | nvarchar2(60) |
| admin_user | admin user | nvarchar2(60) |
| admin_password | admin_password | nvarchar2(60) |
bmsales_users table
| Column Name | Display Name | Type |
|---|---|---|
| bmsales_users_id | bmSales users | varchar2(32) |
| ad_client_id | client | varchar2(32) |
| ad_org_id | organization | varchar2(32) |
| isActive | active | char(1) |
| created | created | date |
| createdBy | created by | varchar2(32) |
| updated | updated | date |
| updatedBy | updated by | varchar2(32) |
| bmsales_configuration_id | bmSales configuration | varchar2(32) |
| ad_user_id | user | varchar2(32) |
bmsales_user_route table
| Column Name | Display Name | Type |
|---|---|---|
| bmsales_user_route_id | user route | varchar2(32) |
| ad_user_id | user | varchar2(32) |
| ad_client_id | client | varchar2(32) |
| ad_org_id | organization | varchar2(32) |
| isActive | active | char(1) |
| created | created | date |
| createdBy | created by | varchar2(32) |
| updated | updated | date |
| updatedBy | updated by | varchar2(32) |
| bmsales_users_id | bmSales users | varchar2(32) |
| m_warehouse_id | route | varchar2(32) |
| route_day | route day | char(1) |
bmsales_shipping_route table
| Column Name | Display Name | Type |
|---|---|---|
| bmsales_shipping_route_id | bmSales configuration | nvarchar2(32) |
| ad_user_id | user | varchar2(32) |
| ad_client_id | client | varchar2(32) |
| ad_org_id | organization | varchar2(32) |
| isActive | active | char(1) |
| created | created | date |
| createdBy | created by | varchar2(32) |
| updated | updated | date |
| updatedBy | updated by | varchar2(32) |
| bmsales_configuration_id | bmSales configuration | nvarchar2(32) |
| m_warehouse_id | route | varchar2(32) |
Development of the process to upload the data to Elondra's server
This process will be called Upload data to Elondra server, it will be done using DAL to access the data stored in Openbravo ERP and ServerProxy to upload the data to the Elondra Server defined in the configuration window.
It will upload data from the defined default organization or its parents from different entities.
Users
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| users.user_id | text(32) | bmsales_users.bmsales_users_id | nvarchar2(32) |
| users.mobile_telephone_number | text(16) | bmsales_users.telephone_number | nvarchar2(16) |
| users.name | text(32) | bmsales_users.name | nvarchar2(32) |
Taxes
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| taxes.custom_id | text(16) not null | c_taxcategory.c_taxcategory_id | varchar2(32) |
| taxes.tax_name | text(32) not null | c_taxcategory.name | nvarchar2(60) |
| taxes.percent | double not null | default to 0 | number |
| taxes.overcharge | double not null | na | na |
| taxes.refraction | double | na | na |
Note: overcharge is only needed for Spanish professionals who support a higher tax per each type. This will be removed on the next versions of bmSales, and can be safely set to 0.
Units of Measure
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| units.unit_name | text(32) not null | c_uom.name | nvarchar2(60) not null |
| units.description | text(128) | c_uom.description | nvarchar2(255) |
Product Categories
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| categories.custom_id | text(32) not null | m_product_category.m_product_category_id | nvarchar2(32) not null |
| categories.category_name | text(128) not null | m_product_category.name | nvarchar2(60) not null |
| categories.description | text(128) | m_product_category.description | nvarchar2(255) |
| categories.parent_id | text(32) | na | na |
| categories.negotiable | boolean (def. true) | na | na |
Price lists
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| customer_categories.custom_id | text(32) not null | m_pricelist.m_pricelist_id | nvarchar2(32) not null |
| customer_categories.cat_name | text(32) not null | m_pricelist.name | nvarchar2(60) not null |
| customer_categories.description | text(128) | m_pricelist.description | nvarchar2(255) |
Routes
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| routes.custom_id | text(16) not null | m_warehouse.value | nvarchar2(40) not null |
| routes.route_name | text(40) not null | m_warehouse.name | nvarchar2(60) not null |
| routes.description | text(256) | m_warehouse.description | nvarchar2(255) |
Note: Only warehouses with the isShipper flag set to 'Y'.
User Routes
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| user_routes.user_id | text(32) not null | bmsales_users.name | nvarchar2(32) not null |
| user_routes.route_id | text(16) not null | m_warehouse.value | nvarchar2(40) not null |
| user_routes.route_day | integer | bmsales_users_routes.route_day | char(1) |
Customers
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| customers.custom_id | text(32) | c_bpartner.value | nvarchar(40) |
| customers.cust_name | text(64) | c_bpartner.name | nvarchar2(60) |
| customers.company_name | text(64) | c_bpartner.name2 | nvarchar2(60) |
| customers.contact_name | text(32) | c_bpartner. | |
| customers.contact_title | text(32) | c_bpartner. | |
| customers.fiscal_id | text(16) | c_bpartner.fiscalcode | nvarchar2(16) |
| customers.email | text(64) | c_bpartner. | |
| customers.web_site | text(64) | c_bpartner.url | nvarchar2(120) |
| customers.route_id | text(16) | na | na |
| customers.local_address | text(128) | c_bpartner. | |
| customers.local_phone | text(32) | c_bpartner. | |
| customers.customer_category_id | text(32) | c_bpartner.m_pricelist_id | nvarchar2(32) |
| customers.comm_time_frame | text(32) | na | na |
| customers.ship_time_frame | text(32) | na | na |
| customers.visit_time_frame | text(32) | na | na |
| customers.notes | text(128) | na | na |
| customers.city | text(64) | c_bpartner. | |
| customers.province | text(64) | c_bpartner. | |
| customers.pais | text(64) | c_bpartner. | |
| customers.postal_code | text(15) | c_bpartner. | |
| customers.exclude_vat | boolean | c_bpartner.istaxexempt | char(1) |
| customers.vat_recharge | boolean | na | na |
| customers.vat_invoice | boolean | na | na |
| customers.user1 | text(128) | na | na |
| customers.user2 | text(128) | na | na |
| customers.user3 | text(128) | na | na |
| customers.user4 | text(128) | na | na |
| customers.user5 | text(128) | na | na |
| customers.user6 | text(128) | na | na |
| customers.longitude | double | na | na |
| customers.latitude | double | na | na |
| customers.geo_timestamp | date time | na | na |
| customers.payment_type_id | text(8) | c_bpartner. |
Customer Routes
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| customer_routes.customer_id | text(40) not null | c_bpartner.value | nvarchar2(40) not null |
| customer_routes.route_id | text(16) not null | m_warehouse.value | nvarchar2(40) not null |
| customer_routes.seqNo | integer | m_warehouse_shipper.seqNo | number(10,0) |
Products
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| products.custom_id | text(16) | m_product.value | nvarchar2(40) |
| products.name | text(32) | m_product.name | nvarchar2(60) |
| products.description | text(256) | m_product.description | nvarchar2(255) |
| products.tax_id | text(16) | m_product.c_tax_category_id | nvarchar2(32) |
| products.selling_unit_id | text(32) | m_product.c_uom_id | nvarchar2(32) |
| products.measuring_unit_id | text(32) | m_product.c_uom_id | nvarchar2(32) |
| products.sell_2_meas_factor | double | calculated | 1 |
| products.category_id | text(32) | m_product.m_product_category_id | nvarchar2(32) |
| products.bar_code | text(20) | m_product.upc | text(30) |
| products.weight | double | m_product.weight | number |
| products.volume | double | m_product.volume | number |
| products.user1 | text(128) | na | na |
| products.user2 | text(128) | na | na |
| products.user3 | text(128) | na | na |
| products.user4 | text(128) | na | na |
| products.user5 | text(128) | na | na |
| products.user6 | text(128) | na | na |
Prices
Relation of needed info in Elondra's server and its mapping with the equivalent in Openbravo ERP
| Elondra Column | Type | Openbravo ERP Column | Type |
|---|---|---|---|
| prod_prices_by_cust_cat.product_id | text(16) | m_product.value | nvarchar2(40) |
| prod_prices_by_cust_cat.customer_category_id | text(32) | m_pricelist_version.m_pricelist_id | text(32) |
| prod_prices_by_cust_cat.price | double | m_productprice.pricestd | number |
| prod_prices_by_cust_cat.price_min | double | m_productprice.pricelimit | number |
Development of the process to download the orders from Elondra's server
This process will be called Download data from Elondra server. It will get the orders from the elondra server using ServerProxy and insert them in Openbravo ERP using DAL. The orders will be completed after being inserted.
Order
Relation of the data needed in Openbravo ERP and data retrieved from Elondra's server.
DB table name = C_Order
Class name = Order
| Openbravo ERP DB column name | Openbravo ERP column name | Type | Elondra Column | Type | Comment |
|---|---|---|---|---|---|
| c_order_id | ID | nvarchar2(32) not null | na | Assigned by DAL | |
| ad_client_id | Client | nvarchar2(32) not null | na | Retrieved from the context | |
| ad_org_id | Organization | nvarchar2(32) not null | na | retrieved from the configuration | |
| isactive | Active | char(1) not null | na | set to True | |
| created | created | date - not null | na | set to today | |
| createdby | createdby | nvarchar2(32) not null | orders.user_id | text(32) Not null | |
| updated | updated | date not null | na | set to today | |
| updatedby | updatedby | nvarchar2(32) not null | orders.user_id | text(32) Not null | |
| issotrx | Sales Transaction | char(1) not null | na | set to True | |
| documentno | Document No | nvarchar2(32) not null | na | Assigned corresponding sequence for document type | |
| docstatus | Document Status | nvarchar2(60) not null | na | set to 'DR' | |
| docaction | Document Action | nvarchar2(60) not null | na | set to 'CO' | |
| c_doctype_id | Document type | nvarchar2(32) not null | na | Set to '0'. New. | |
| c_doctypetarget_id | Transaction Document | nvarchar2(32) not null | orders.order_type_id | text(16) Not null | |
| dateordered | Order Date | date not null | orders.Created_Date | Date not null | |
| datepromised | Scheduled delivery date | date not null | orders.delivered_date | Date | |
| dateacct | Accounting Date | date not null | orders.created_date | Date not null | |
| c_bpartner_id | Business Partner | nvarchar2(32) not null | orders.customer_id | text(32) Not null | |
| c_bpartner_location_id | Partner Address | nvarchar2(32) not null | na | Retrieved from Business Partner | |
| BillTo_id | Invoice Address | nvarchar2(32) not null | na | Retrieved from Business Partner | |
| delivery_location_id | Delivery Location | nvarchar2(32) not null | na | Retrieved from Business Partner | |
| poreference | Order Reference | nvarchar2(20) not null | orders.custom_id | Text(32) Not null | |
| c_currency_id | Currency | nvarchar2(32) not null | na | Retrieved from Business Partner | |
| paymentrule | Form of payment | nvarchar2(60) not null | na | Retrieved from Business Partner | |
| c_paymentterm_id | Payment terms | nvarchar2(32) not null | na | Retrieved from Business Partner | |
| invoicerule | Invoice term | nvarchar2(60) not null | na | Retrieved from Business Partner | |
| deliveryrule | Delivery term | nvarchar2(60) not null | na | Retrieved from Business Partner | |
| freightcostrule | Freight cost rule | nvarchar2(32) not null | na | set to 'I' | |
| deliveryviarule | Delivery method | nvarchar2(32) not null | na | Retrieved from Business Partner | |
| priorityrule | Priority | nvarchar2(32) not null | na | set to '5' | |
| TotalLines | Summed Line Amount | number | na | set to '0'. Calculated after. | |
| grandtotal | grand total amount | number | na | set to '0'. Calculated after. | |
| m_warehouse_id | Warehouse | nvarchar2(32) not null | na | Retrieved from the context | |
| m_pricelist_id | Price List | nvarchar2(32) not null | na | Retrieved from Business Partner | |
| description | Description | nvarchar2(32) not null | orders.comments | Text(128) | Set to: 'Document: custom_id imported from bmSalesOb. Comments |
| SalesRep_ID | Sales representative | nvarchar2(32) not null | orders.user_id | Text(16) Not null | |
| AD_user_id | User Contact | nvarchar2(32) not null | na | Retrieved form the business partner |
Orderline
Relation of the data needed in Openbravo ERP and data retrieved from Elondra's server.
DB table name = C_Orderline
Class name = Orderline
| Openbravo ERP DB column name | Openbravo ERP column name | Type | Elondra Column | Type | Comment |
|---|---|---|---|---|---|
| c_orderline_id | ID | nvarchar2(32) not null | na | Assigned by DAL | |
| ad_client_id | Client | nvarchar2(32) not null | na | Retrieved from the context | |
| ad_org_id | Organization | nvarchar2(32) not null | na | retrieved from the configuration | |
| isactive | Active | char(1) not null | na | set to True | |
| created | created | date - not null | na | set to today | |
| createdby | createdby | nvarchar2(32) not null | orders.user_id | text(32) Not null | |
| updated | updated | date not null | na | set to today | |
| updatedby | updatedby | nvarchar2(32) not null | orders.user_id | text(32) Not null | |
| c_order_id | Sales Order | nvarchar2(32) not null | order_items.order_id | Text(16) | retrieved from the OB order with the order_items.order_id. |
| line | Line No | number not null | na | Calculated automatically | |
| dateordered | Order Date | date not null | na | Retrieved from the order header | |
| datepromised | Scheduled delivery date | date not null | na | Retrieved from the order header | |
| m_warehouse_id | Warehouse | nvarchar2(32) not null | na | Retrieved from the order header | |
| m_product_id | Product | nvarchar2(60) | order_items.product_id | text(16) not null | |
| c_uom_id | UOM | nvarchar2(60) not null | na | Retrieved from the order product | |
| qtyordered | Ordered Quantity | numeric not null | order_item.qty_requested | number not null | |
| qtyreserved | Reserved Quantity | numeric not null | na | set to "0" initially | |
| qtydelivered | Delivered Quantity | numeric not null | na | set to "0" initially | |
| qtyinvoiced | Invoiced Quantity | numeric not null | na | set to "0" initially | |
| c_currency_id | Currency | nvarchar2(32) not null | na | Retrieved from the order | |
| pricelist | List Price | numeric not null | order_item.official_price | numeric | |
| priceactual | UnitPrice | numeric not null | order_item.custom_price | numeric | |
| pricelimit | Price Limit | numeric not null | order_item.official_price | numeric | |
| linenetamt | Line Net Amount | numeric not null | na | Calculated automatically. | |
| freightamt | freight Amount | numeric not null | na | set to "0" initially | |
| c_tax_id | Tax | nvarchar2(32) not null | na | Retrieved from the business partner |
Implementation of the scheduling capabilities to both processes
Based on the Openbravo Process Scheduling Capabilities, both processes will provide a very flexible range of scheduling options will be provided. This will enable the user to configure both processes to run periodically. This implementation will be done following the instructions explained in the process scheduling chapter in the developers guide.