ERP/Dual UOMs

Project Background

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