Project Background

edit

User 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
edit