Posts Tagged ‘Enterprise resource planning’

Calculating a product cost with OpenERP

Last week I had the opportunity to try a very useful module that, IMHO, should be included in OpenERP core. This module is product_extended and can be found in the extras repository, you can download it with the following command:

#  bzr branch lp:openobject-addons/extra-6.0

This module does many things, among them shows the product last order for a supplier and its date. But what is really interesting is that it calculates a product cost based on its Bill of Materiales. In order to do that, first you need to check the “Compute Standard Price” check box in the product form,

Then, you can either click on the “Compute Standard Price” button or check it in the product bill of materials (a product can have more than one bill of materials). In the latter, you can find the product information on the Bill of Materials form.

You can calculate the standard price by clicking on the “Compute Standard Price” wizard on the right of the BoM form. How is this cost calculated? This is the interesting part, it takes into account the product cost of each product, plus the hours and cycle costs of every workcenter that is involved in its routing. A very useful module.

It needs a minor change in order to make it work with OpenERP 6.1 but you should try it, I highly recommend it


Data integrity issues in MRP implementations

December 22, 2011 Leave a comment

A good description of the data integrity issues found in MRP implementations can be found in the article on MRP in Wikipedia. In a nutshell, if the

English: MRP

Image via Wikipedia

information in your manufacturing system is bad, don’t expect its planning to be any better (or GIGO). While you are implementing the MRP module in OpenERP, you should pay attention to the following information items in your systems:

  • Lead-times: critical for the timing phase of your planning. If production or supplier lead-times are not accurate, don’t be surprised to find planning dates that are not realistic.
  • Stock levels: very critical, since all the procurements are created when the stock levels drop down below its reorder point or when a customer makes an order (depending on how you have your system set-up)
  • Bills of Materials: your system will procure either raw material, work in process inventory or end-items. Which items it will procure depends on the accuracy of Bill of Materials. Your BoM needs to reflect how your product is assembled. I found some places where the BoM only reflects the cost structure of the end-item, and that is wrong. The cost of the end-item is a different problem that needs to be addressed in a different module than the manufacturing module.
  • Products: your system needs to have all the products you procure (either by purchasing or manufacturing) and sell. And its description needs to be complete.
  • MPS, or production schedule: needs to be available in order to make the plan. In all the manufacturing plants there is a production schedule. People might not be aware there is one, but actually you can find it in the daily or weekly manufacturing plans.
  • Reorder points: you need reorder points (minimum and maximum stock levels) in some products in order to not disrupt your quality of service. We will not get into the calculation of these items (by now)

    We will get into more detail on these items and how to address them in further posts


Importing products with web-services in OpenERP

Python logo

Image via Wikipedia

Yesterday someone at the OpenERP forum asked me for an example of how to import product data into OpenERP, so here it is, below you will find an example of a Python script for loading basic product data.

#!/usr/bin/env python
# coding: utf-8

import xmlrpclib
import csv

username = ‘admin’ #the user
pwd = ‘pwd_admin’ #the password of the user
dbname = ‘blog_db’ #the database

# Get the uid
sock_common = xmlrpclib.ServerProxy (‘http://localhost:8069/xmlrpc/common’)
uid = sock_common.login(dbname, username, pwd)

#replace localhost with the address of the server
sock = xmlrpclib.ServerProxy(‘http://localhost:8069/xmlrpc/object’)

#load categories first
filename = “categories.csv”
reader = csv.reader(open(filename,”rb”))
for row in reader:
category = {
‘name’: row[1],
‘active’: True,
category_id = sock.execute(dbname, uid, pwd, ‘res.partner.category’, ‘create’, category)
print category_id

print “End categories load”

filename = “product_template.csv”
reader = csv.reader(open(filename,”rb”))
for row in reader:
product_template = {
‘name’: row[0].rstrip(),
template_id = sock.execute(dbname, uid, pwd, ‘product.template’, ‘create’, product_template)
print template_id

product_product = {
‘default_code’: row[0].rstrip(),
‘active’: True,
product_id = sock.execute(dbname,uid,pwd,’product.product’,’create’,product_product)
print product_product

print “End product load”

This is a simple script you can execute from the command line. In summary, first you need to create the categories and then you need to create the product.template objects and product.product objects. If you have any question about this, please let me know.


OpenERP and E-Commerce

Shield logo for Ubercart project

Image via Wikipedia

Last week a client asked me if OpenERP had an E-Commerce solution, and she was disappointed when I said no. But she was glad when I explained her why it was already integrated with existing open-source e-commerce solutions, such as Magento. And I wanted to share my thoughts on this, since some people keep asking for integrated e-commerce functionality (as well as business intelligence functionality).

To the best of my knowledge, to this day OpenERP already has connectors to Magento Commerce and Joomla. Does not have connectors to Drupal (Ubercart and Drupal Commerce projects) but I expect OpenERP to have those connectors soon since the Drupal community is growing fast (actually I am really tempted to develop a connector to Drupal Commerce but first I need a project to fund the development, and I need the time. Plus, Drupal Commerce first has to release an stable release). Which means, you can install your e-commerce solution, and integrate it with OpenERP.

What areas do you have to integrate? The product catalog comes to my mind (and the ability to manage multiple pictures per product). Then the sales orders, invoices, price lists and the delivery and inventory information. Those are the information items that should be available to the e-commerce solution, in order to make it work properly. Then the customer information, such as credit limits and order history. Lots of information that needs to be pulled from OpenERP.

OK, now why it is not a good idea for OpenERP to be a full-fledge e-commerce solution. Because the ERP and MRP problems are big enough. That’s why. E-Commerce is a different beast on its own, that demands other skills. Such as having the ability to make the page look good and the capability of implementing a design in your page. Plus, the page needs to be optimized for search, and the e-commerce solution needs to manage content the way a CMS does. That is quite an stretch for an ERP system. Ah! Have I mentioned that e-commerce packages need to integrate with payment gateways and sometimes need to be multi-lingual?

At the end of the day, the OpenERP community is not big enough to justify the development of an e-commerce solution on its own, and the e-commerce packages around already do an outstanding job. We will be much better off focusing on the development of the integration connectors, rather than developing an e-commerce solution from the ground-up.


Safety Stock and reorder points in OpenERP

Curves of the cycle service level & fill rate ...

Image via Wikipedia

Safety Stock is the level of stock maintained to mitigate the risk of stockouts. Do you have safety stock in OpenERP? It depends. If you are running a manufacturing operation, and you manufacture to stock or build to order while keeping raw material inventory on-hand, you are actually using safety stocks. Chances are, if you don’t have safety stock you will run risks of stockouts and customer service deterioration.

Do you have a module or something like that in OpenERP? Actually no. And there might be a reason for that. But we will not go into that, we will show how to manage safety stocks and reorder points in OpenERP.

What you do have in OpenERP is the possibility of defining Minimum Stock Rules in your products. You need to do so if you are planning on using the MRP module. Actually it is mandatory to have minimum stock rules for all the raw material that you need in order to manufacture products. And you need to have minimum stock rules for your work-in-progress components in place when you build to stock work-in-progress components, or products.

The Minimum Stock Rules form allows you to define two quantities for your product. First the minimum quantity. This is a number that when it is reached by the virtual inventory, OpenERP creates a procurement in order to increase the stock of the product up to the Maximum Quantity amount (which is the second quantity in the Minimum Stock Rule).

And if you think about it, since all you need is to calculate your minimum and maximum quantities for your product, you don’t need to calculate your safety stock since it is already taken care of by the minimum quantity.

Now, how do you calculate your minimum quantity? You can do it with the following formula in LibreOffice:

Minimum Quantity = NORMINV(Service Level, Average Demand During Lead Time, Standard Deviation of Demand During Lead Time)

It’s a simple formula that can be used in LibreOffice or PostgreSQL (you need to install pgnumerics though, not a big deal)

Service level is the % of orders that you are planning to fulfill. If you are planning on satisfying average demand, you need to set it to 50%. If you are planning on satisfying 95% of your orders, you need to set it at 95%.

Average and Standard Deviation of Demand during lead time are quite straight-forward parameters. In order to calculate them you need to take all your orders you took during a period of time, and then calculate its average and standard deviation.

The minimum quantity  is composed of two components, the safety inventory piece and the lead-time demand piece. Lead-time demand can be calculated as the average demand you have for that product, and the safety stock can  be calculated as

Safety Stock = Minimum Quantity – Lead Time Demand

At this moment we are developing a module that calculates Minimum Quantity, Safety Stock and Lead Time Demand. We will keep you posted on this.


HR Attendance analysis with OpenERP

March 27, 2011 2 comments
KDE 4.0

Image via Wikipedia

My partner, Cristian Sebastian Rocha, has been working the past three months in the development of the hr_attendance_analysis module, which, as its name suggests, helps companies analyze their employees attendance.

This module allows you make calculations on the worked hours by employees. You can apply formulas and calculation rules in order to automate attendance calculation. It can also be integrated with other HR related modules, such as the clock_reader module.

This module also has a workflow for confirming the entered date on each document type, journal and payroll. The first document holds daily attendance information for the employee, whereas payroll holds payroll information on a monthly, bi-weekly or daily basis.

These are some screenshots for the module:

In this image you can see the main menu for accessing the HR module. You can also see the menu items for accessing the Payroll documents (selected) and Attendance Journals.

In this image you can see the document with the summary of employee hours (filtered by a date range). You can leave notes for each employee or for the document. It can have two states: draft and validated. We still have to develop the workflow for cancelling and re-setting the document.

In this screenshot we can see the entry for the employee Journal. Here you can see in detail the in and out time for a given date. You can also see the vacations or time off hours (such as late arrivals, early departures, sick days, etc.) To the left you can also see the result of the hours calculation.

This is a formula for summarizing the values stored in the journal, in order to be shown in the Payroll document.

This is a formula for calculating normal hours in a given  date for a given employee:

In this screenshot, you can see a rule. This rule assigns reasons for the actions of entering or leaving the plant. IN this case we can see how to calculate an early leave without a warning.

We still have things to do, but soon we will upload the module to LaunchPad. If you have any comments, or suggestions, they are more than welcome.


Thoughts on why some SMBs will switch to OpenERP over traditional ERP systems

Python logo

Image via Wikipedia

Today I had the chance to learn about the features of a propietary ERP system. This gave me a good  insight of why small businesses are moving to OpenERP. Some of the features customers appreciate are:

  • Customers can customize OpenERP. I did not realize it, but there are many systems around that are closed and can not be modified. And making changes to it means making changes to its core. And this could take months. This is the opposite of OpenERP, just look at OpenObject and you can see how easy it is to extend or override its functionality. Also, workflow functionality is quite powerful. Not all ERPs have the capability of changing its behaviour as OpenERP does.
  • Customers can look at the code. This might sound paranoid, but gives them confidence on the system.
  • No vendor lock-in. Customers love this. The fact they don’t have to stop the plant in order to switch vendors is highly appreciated. And being capable of keeping the code and giving its customization to someone else who is capable of maintaining it, makes life easier for our clients.
  • Support forums
  • Have I mentioned Python? OK, not all the IT people I know have ever heard of Python, but the people I met today were running their ERP system with Centura, which is a framework based on SQLWindows, one of the first object oriented programming languages in the mid-90s. With this I mean… OpenERP runs with one of the most productive programming languages in the world. Plus, even though we are not millions, the group of people working with Python is growing every day.
  • TCO. This is a big deal. Not only the licenses (all the ERP systems I see over here have licenses that are more expensive than $40,000) but the hardware and software as well. You only need a browser on your desktop to use OpenERP, no Windows licenses. And no server license as well. This saves thousands and thousands of dollars.
  • And last but not least, the web client. This makes the life of the system administrator much easier