ERP/Dual UOMs
< ERP
Project Background
editUser Requirement
edit- 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
edit- 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
edit- 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
edit- 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
edit- 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
edit- $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
edit- 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
edit- 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
edit- 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
edit- 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
edit- Return to Open Source ERP