Hierarchal price lists or how to deal with multiple suppliers with multiple price lists

ERPNext
1

I honestly can’t believe that I cannot find anything on this topic …

It is an absolutely normal process for any business to have multiple pricelists from multiple suppliers on a huge range of input products repeating for each supplier (think RFP processes for a range of products from multiple suppliers).

Why is it not possible to derive a consolidated master purchase list that automatically picks the cheapest price + supplier for every product belonging to purchasing price lists?

I can seriously programm the SELECT statement for that in around 15min, never mind actually using pythonic methods in a script … what am I missing here? Why am I not seeing an easy way on how to create such a consolidated list automatically?

2

Hi:

I’m not an expert but …

A list with the product, lowest rate and the supplier for each product is ok?

In this case you are right, this seems doable with a single report … but … what happens with UOMs? Valid from and Valid upto dates?

I’m just talking about prices lists. Note that pricing rules and promotional schemes are even more complicated: discounts, mixing combinations, crossed buyings, periods, min qtys … Is not achievable with a simple SQL query …

Hope this helps.

1 Like
3

thanks for the reply:

Precisely. That is all I need.

I have a solar install business, so I will create BOM-Subcomponents, and out of those subcomponents I will create customer products, and out of those products I will create customer offers.

I need these consolidated price-lists simply for manufacturing and planing of my BOM-Subcomponents.

UOMs? I always work with normalized units :wink:
Why should I care about Valid from, upto dates? There must be only one type of price-list: a price-list with valid data … separation of concerns, one step at a time.

Why should I care? Keep things simple and organized one step at a time. All of that is merely the last step of an offering.

4

Hi:

I understand that you don’t need features like UOMs, periods, etc … but a ERPNext standard process have to consider all the possible scenarios.

So, you requirement is a “non-standard” requirement. I think should be covered with a custom report.
It’s really easy to get this kind of reports based on SQL querys. Have you some experience with this?

We can help

5

Thanks for your time! :slight_smile:

I am by profession a microservice programmer in Java and JS, so yes, I a can do a fair amount of SQL. Where can I read up on custom reports? I also haven’t yet had a look at the DB and its schema.

Edit:
found this: Query Report

now I need to expore the DB schema…

6

There are a lot of resources to learn about query reports.

Official docs
https://frappeframework.com/docs/v14/user/en/desk/reports/query-report

Video:

You can use console on ERPNext GUI to explore database (login as admin). Search “System Console” on Awesome Bar. If you have access to the server terminal, you can use mariadb console

bench mariadb

I think that a good approach could be selecting all the items table and make subselects with item price inner join price lists, to get the lowest rate of each one, provider data, etc …

Items → tabItem
Item Price → tabItem Price
Price list → tabPrice List

If you are working with BOM, Material requests … you can start from there … A good idea could be make a report from the document to get prices just for the items involved on them, avoiding get all the items on the database .

Welcome! Get fun! Claim for help if you need it!
Hope this helps.