ERP/Dual UOMs

User Requirement

 * A client purchase materials say metal sheets measured in metric cubes (M3) and sells them in another UOM (Unit of Measure) say Square Feet sheets (SF).
 * To show the UOM quantities and relative prices easily during the sales and cost estimation process
 * To manage such selling and purchasing prices for all materials efficiently in the system

System Challenge

 * The challenge is to incorporate such requirement into an Open Source ERP Application suite
 * A Sales Order enters the order in Qty (no of crates) and a Callout code converts to the target UOM
 * Integrating with the PriceList Schema and Sales/Purchase modules

Unit Of Measure Formula

 * Client purchase material measured in M3 according to the supplying vendor or seller.
 * The Quantity of that material (XY) is converted to SFS which will give another Qty thus: XY = A(M3) <> B(SF)
 * For example XY =
 * Formula is
 * M3= T(m) x W(m) x L(m)
 * MSF= W(ft) x L(ft) / 1,000
 * Where:
 * T = Thickness
 * W = WIdth
 * L = Length

Presentation of Respective Pricings

 * Base Price is stored in Purchase PriceList
 * What is normally maintained from the supplying vendor
 * Prices are shown in respective UOMs
 * Selling price is thus converted and displayed as a virtual column

Sample PriceList Schema

 * Price Selling(M3) = a
 * Price Purchase(M3) = a X 0.9
 * Margin = 10%
 * Virtual Selling (SFS) = a X T / 10.7497
 * Virtual Purchase (SFS) = 0.9 X a X T / 10.7497

Conversion Formula

 * $a * T / 10.7497
 * Price multiplies by Thickness divide by Area conversion factor from metric unit to imperial unit for surface area

Proof of Concept

 * Next, we have to deploy a POC using an Open Source ERP such as ADempiere
 * The POC needs to demonstrate the operation of a Sales Order showing the above price and virtual price
 * Some necessary cusomisation to cover gap if any
 * Changes done are minimal. Further complete presentation of requirement is elaborated further after this.

POC Gap

 * Buying and selling price virtual columns adding in the M_ProductPrice table
 * CV_BuyPrice NUMBER(10,2)
 * CV_SellPrice NUMBER(10,2)
 * SQL to extract corresponding values according to the formula.
 * Using thickness value during Product Setup via Attribute Set

POC Steps

 * Setup Pricelist record
 * Setup Product Record
 * Setup Atribute instance for T X L X W
 * Create DB virtual column
 * Apply SQL formula to column
 * Config AD Window/Tab/Column for Sales Order Detail Lines to show virtual columns
 * Virtual columns to show both buying and selling price
 * Replicate in Project(Order) for Sales margin planning

Demo Steps

 * Launch Application
 * Call up a Sales Order window
 * Select BPartner
 * Select Pricelist
 * Go to OrderLine Detail Tab
 * Select Product ID
 * View displayed prices, both base and virtual
 * Complete and print out Sales Order

Other Links

 * Return to Open Source ERP