Pandas is a very popular open-source Python programming library that is commonly used for data science. It can perform almost all SQL-like operations like SELECT, WHERE, GROUP BY, JOIN, UNION, UPDATE, DELETE, and many more and is very helpful when dealing with large data sets.
PostgreSQL outperforms pandas when working with standalone operations like filter, groupby, sort, and join, but when it comes to complex real-world queries pandas is much faster. This is because a database gives you stuff like concurrency, locking, indexing, etc., which costs some execution time. PostgreSQL is also not designed for transforming data in certain ways. PostgreSQL isn’t good for describing sets of rows, e.g. weighted moving average, string manipulation, regex, and pivot tables. So for such complex reports, it is always safer to use pandas data frames.
Let me know of other Python libraries that you have found helpful in optimizing performance.
1. mass_editing: This module provides easily configurable bulk editing options.
2. auth_admin_passkey: This module allows logging in with an extra system administrator password in other user accounts.
3. report_xlsx: This module provides a basic report class to generate xlsx report.
4. auditlog: This module allows the administrator to log user operations performed on data models such as create, read, write, and delete.
5. web_m2x_options: This module modifies “many2one” and “many2manytags” form widgets so as to add some new display control options. The best part is that it supports global option management with ir.config_parameter.
6. web_export_view: This module allows exporting the tree view data to CSV/XLS
Let me know if you guys know of more such essential apps.
First, let us talk about expense booking operation in proprietary ERPs. The screen has multiple options to select from like the Internal Order, Cost Centers, Profit Centers, WBS, and so on. So, this means the accounting user should be educated enough to choose the right field. This can be a real challenge for SMEs.
However, in Odoo we just have one field which covers all the above operation- Analytic Account. The beauty is AA is tightly integrated with almost all the apps. It can therefore be configured as a cost center, profit center, internal order, projects, and many more. Moreover, it can be linked with Equipment, Fleet, and so on to capture its total expense over a period. And if a company is making a profit by leasing its equipment or fleet then AA can be linked-to Sales order to track its total profitability. So, you see it is super easy for the accounting user.
Now, the next question is how to differentiate among different types of AA? It is simple. For that, we have Analytic Groups and Tags. So, you see the complexity of managing various operation is moved from the transactional data to the master data.
Next, is how do we restrict the user to a particular type of AA. Again, by using record rules we can restrict users to only certain types of AA and its corresponding entries.
I am writing this blog after a very long time. Over the past few months, I was exploring the strengths of python language and realize it is just amazing when it comes to Machine Learning. The ultimate goal of ML algorithms is to be able to take decisions without any human intervention.
After implementing large scale multi-country multi-company Odoo projects, I realize it is time to take Odoo customization to next level and develop apps which helps business in making strategic decisions by projecting future sales. Here, is one such app developed by me- Demand Planning.
This app can be used to analyze your historical sales, project your future demands, and then create orders according to a supply plan that will add stock as needed. Demand Planning App works in 3 stages:-
Demand Plan– This will calculate the expected future demand for a product based on historical demand. It is here where various time series forecasting models are being used.
Supply Plan– This provides a list of purchase and manufacturing orders. It considers lead times so that orders are placed in time. Purchase orders generated uses the first vendor from the product master which can be changed later. For assembly products, all sub-components are considered and appropriate manufacturing or purchase orders are created for raw materials.
Order Creation– This allows the actual creation of purchase and manufacturing orders from locked supply plans.
In my previous blog, I published use of Weight and Price Barcode. In this blog, we will see how with slight customization of point of sale module we can make use of combination of weight and price barcode.
Let me first start with 2D barcode. In this type, we can merge multiple barcodes into one. Example of 2D barcodes are DataMatrix, PDF417, QRCode, Aztec, etc. Here, is a sample output when a 2D barcode is scanned- 3330012244123,3330013355123,3330014466123.
Barcodes can be separated either by comma or tab or newline character. Now, this structure can be utilized in Point of Sale for selling combo products. Consider an example. We are selling Boni Oranges and Golden Apples wrapped in a decorative tray. Now, the total price of the product includes weight of two fruits plus cost of the tray. Hence, we can combine 2 weight barcode and 1 price barcode into a 2D barcode.
In a manufacturing process, we might have to outsource some or all of the operations. Here, two things are involved-
I. Stock Management
We have to make a stock move from Raw Material location to Contractor (Production) location. To do this, we set Production location on the relevant Routing document.
The production location should be configured with the following data:
Location Type: Supplier,
Location Address: Select an address of the subcontracting partner,
Chained Location Type: Fixed,
Chained Location if Fixed: your Stock,
Chaining Lead Time: number of days before receipt of the finished product.
However, if the Contractor location is different for each Work Center operation, then we have to customize it.
In order to achieve this we first have to define Analytic Account. It has Customer field where we have to select the Partner to whom this process is going to be outsourced.
Next, in Work Center configuration we set the Hour Account (for discrete manufacturing) or Cycle Account (for continuous manufacturing).
Finally, when manufacturing process is completed system generates Analytic Entries which can be used for invoicing our sub-contractor. Using the “Invoiceable” field, we can either Invoice full 100% or do partial invoicing (90%,80% and so on).
Finally, using the More button we can Create Invoice. We can create Invoice for each Analytic item separately or merge multiple items belonging to same Contractor.
On clicking Create Invoice button, an Invoice gets created which can be validated and followed up for payment.
Here, we have to replace Customer Invoice with Supplier Invoice so that outsourcing cost is booked as expense and not income.
i. Real-time Accounting Valuation
ii. Analytic Accounts
Real-time Accounting Valuation
Here, each stock movement generates a corresponding accounting entry in an accounting journal. You need to configure your Raw Material location and/or Production location and Finished Products Location in Routings.
Next, set up a general account for each location that should be valued in your accounts. If a product goes to one location or another and the accounts are different in the two locations, Odoo automatically generates the corresponding accounting entries in the accounts, in the stock journal.
Remember, in the Product form, go to the Accounting tab and select the Real Time(automated) option for Inventory Valuation. To define your accounts, you have two options. Set them on the product category, or on the product.
From the Accounting Stock Properties section, for the Product Category, set the Stock Input Account, the Stock Output Account and the Stock Valuation Account
From the Accounting tab, for the Product, set the Stock Input Account and the Stock Output Account.
You can also overwrite the accounts from the Product or the Product Category by defining Stock Input Account and Stock Output Account for a Location.
Here, is a sample journal entry for stock move of a product from Raw Material location -> Semi Finished Location -> Virtual Location
This helps in valuating different location during a manufacturing operation.
In manufacturing operation, there are 3 types of cost involved- Material, People and Machine. Whenever manufacturing order is created these analytic account gets created automatically. The feature listed here is not available in default installation.
i. Material Cost– This is achieved through Bill of Materials. An analytic entry is made whenever stock move happens from Stock to Virtual location.
ii. People Cost– This is achieved by linking manufacturing with HR time-sheets. This feature is not there in the default installation. Ideally, an analytic account should be created against each manufacturing order. And employees keep on adding line to time sheets with details of the time used on that manufacturing order.
Alternatively, this can also be achieved through Work Center of type Human. We can set an hourly rate for a particular operation. At the end, we put total number of hours consumed and accordingly system calculates the actual cost.
iii. Machine Cost–
For this we have to configure Work Centers of type Material. If it is discrete manufacturing we configure Hour Account and if it is continuous manufacturing we configure Cycle Account. Also, we specify Cost per hour / Cost per cycle for each operation.
Finally, when the manufacturing order is completed system makes analytic entries for each work center operation for which analytic account is configured.
Chart of Analytic Accounts give us complete expense of Material, Human and Machine
There is no direct link between Organization Structure and access rights. In HR Configuration, we define employees of a company. Next, we create user accounts to give employees access to Odoo. We then link Employee to User in Employee form by using the menu Human Resources ‣ Employees.
Each user is then linked to groups which defines its access rights.
Now, we can control approval process using these groups.
For example, in order to allow only sales manager to approve quotations we can give- <button name="quotation_approved" states="confirmed" type="object" string="Approve" class="oe_highlight" groups="base.group_sale_manager" />
Similarly, we can make fields, groups, page visible only to users of a particular groups by using ‘groups’ attribute <field name="incoterm" widget="selection" groups="base.group_user"/>
<group name="sales_person" groups="base.group_user">
<page string="Other Information" groups="base.group_user">
So, this way groups can be used to limit access to directors, managers, users, etc. and approval process at various levels can be achieved.
The current manufacturing module of Odoo has certain missing features-
1. We don’t have configuration for multiple Raw Material Location
2. Issuing products to Production (Semi-finished) location is missing
3. Cannot select another BOM for Manufacturing Order created automatically from Sales Order
4. There is no track of Estimated and Actual Cost
5. No field to track total cost of production
In order to enable creation of Manufacturing Order from Sales Order and also automatic generation of Purchase Requisition for raw materials, the configuration steps is as follows. Also, to achieve the above listed advanced features customization have been done. Here are the steps
Finished Product Configuration– The product is defined as “Stockable”. Costing method should be set to Standard (so that the accounting impact pulls the price from Manufacturing Order). Select routes as Make to Order and Manufacture.
Raw Material Product Configuration– The product should be set to “Stockable” if stock management is required else set to “Consumables”. Costing method should be set to Average (we are assuming here that raw materials will always be bought from Supplier). Select routes as Make to Order and Buy. Default Raw Material location of this product can also be configured under Inventory tab.
Bill of Material Configuration– Goto Manufacturing -> Products -> Bill of Materials in order to define BOM for every product.
Routing Configuration– Next, we define different operations through which the raw materials will go through. We also specify default Raw Materials and Finished Products Location. Make sure right Semi Finished Location is also selected. Once routing is defined we attach it to BOM. Here, we have customized to allow users to add default Raw Material and Finished Product Location so that when Manufacturing Order gets created it pulls the location from here.
Estimating Cost– A new button is provided in Sales Quotation. Based on this estimated cost salesman adds profit margin to get the final sale price of the order.
On clicking, system automatically pulls 2 things-
Raw Materials defined in BOM and
Operations defined in Routing
The subtotal and quantity comes from these configurations. User can click on green button to change the quantity (no. of hours) of an operation. The subtotal and total estimated cost gets adjusted accordingly.
Fetching Estimated Cost in Quotation Line– A button is provided in quotation line as shown below
When the button is clicked then it asks for Profit Margin and accordingly updates the unit price of the quotation line
The price has been updated-
Manufacturing Order– Finally, when the quotation is converted to order then automatically manufacturing order gets created.At this stage, Delivery Order shows Waiting Another Operation as MO needs to be completed
MO created is in draft state so that user can verify all the details before confirming it. Also, a product can have multiple BOM so make sure right one is selected.
(a) Buying of Raw Material
After MO is confirmed, then click on Check Availability to automatically raise Purchase Requisition against the supplier configured in Product Master. If the raw material is already there in stock then click on Force Reservation button as shown in the following diagram
Purchase Requisition gets created. The system smartly merges all products into one which is being bought from one supplier and coming into same Raw Material location. The full workflow is followed and we receive all raw materials.
(b) Issuing Raw Materials to Work in Process locations
Before we purchase raw materials, the Issue Products tab shows products in red. But once the product is purchased and available in stock then the color changes to black. Here, the stock move created pulls source location from the Raw Material Location field configured in Product Master. This way each material can have different source location.
We then click on green button to issue them to Work in Process location
(c) Start Production
Click on Mark as Started button on the header to start production. This will generate costing of BOM in cost breakdown tab.
Cost Breakdown tab lists actual raw material costing
(d) Process Work Orders
Work Orders tab lists all the operations defined in Routing. Number of Hours field is editable so that user can enter the actual no. of hours spent on each operation. As each work order is completed, Cost Breakdown tab lists the actual costing.
(e) Finally when all the work orders are completed then we hit Finish Production. Total cost field shows the actual costing of the product
The MO state now shows Done. The delivery order state also changes to Ready to Transfer
Delivery Order– Finally we transfer the finished product to our customer by clicking on Transfer button.