Product information management for multiple variations where using item variants does not seem to work

ERPNext Stock/Inventory
1

Hi,

I am evaluating ERPNext for my wholesale and retail pharmacy business. On the whole, I am very impressed with many aspects of the system and the value for money that it presents.

I am posting this message as I would like to seek the community’s advice on how to manage my product information.

In terms of product information, a bit of background first. Since we are buying medicines we have two broad categories of medicine: branded and generic. Branded refers to the innovator e.g. Panadol. Generic refers to the non-proprietary name e.g. Paracetamol is the generic name for Panadol. For each product there will be many different pack sizes and strengths. In the UK, the government has come up with a naming scheme as follows:

  1. The Virtual Medicinal Product (VMP) describes the generic title for a product including the form and strength, for example ‘Atenolol 100mg tablets’.
  2. The Virtual Medicinal Product Pack (VMPP)describes the generic title for a generic or proprietary product pack which is known to have been available. The description includes the pack size, for example ‘Atenolol 100mg tablets 28 tablet’.
  3. The Actual Medicinal Product (AMP) describes an actual product which is known to have been available linked to the name of a particular supplier, for example ‘Tenormin 100mg tablets (AstraZeneca UK Ltd)’ (branded) Or ‘Atenelol 100mg Tablets (Teva UK Ltd)’ (generic).
  4. The Actual Medicinal Product Pack (AMPP)describes an actual product which is known to have been available linked to both the name of a particular supplier and information on the pack size of the product, for example ‘Tenormin 100mg tablets (AstraZeneca UK Ltd) 28 tablet 2 x 14 tablets’ (branded) or ‘Atenolol 100mg Tablets (Teva UK Ltd) 28 tablet 2 x 14 tablets’ (generic).

Each of these four types of product have different codes (DMD codes) in the government database. A normalised structure is present so the AMPP will have a reference to an AMP and VMPP which will in turn will have reference to a VMP.

To add to the level of complexity, the DMD code is rarely used when buying or selling the product and therefore two other product codes are used:

  1. We have an industry wide database (CDData) that contains a PIP Code for every product and some of these products are mapped to the DMD database but not all.
  2. Every product has a barcode.

Now this is where the even greater challenge starts. The various wholesalers in the industry will buy whatever is available and then provide price lists with a single Pip Code for a product even though multiple manufacturers make the product. For example, for Atenolol 100mg Tablets 28 as in the example above, the wholesaler will list only one pip code and as a pharmacy when we order, we may get a Teva branded pack but we may also get a Dr Reddys branded product.

As pharmacies, we are not usually concerned with which brand we get though there may be certain instances where if a patient requests it then we can try and procure it. As a business, we have made a strategic decision that so long as we are getting the cheapest pack, we don’t mind what we get.

So my question is how do I accommodate for all of these different variations of the product? I would like to make sure that the following are possible:

  1. Every product should link back to the relevant DMD code.
  2. All generic products should be tracked separately to branded products.
  3. I want to be able to use a barcode scanner to manage stock in the warehouse.
  4. Where there is more than one manufacturer for the product, I want to have a master product to which all the other manufacturer products are linked.
  5. Sales and purchase history should be able to be tracked across all the different levels of product in the hierarchy.

I had a look at item variants and initially thought that I would be able to create the following structure:

  1. Master product
  2. Attribute sets for the following: pip code, barcode and various dmd codes.

However, it seems that one has to specify the attribute values up front so that they can be selected/generated. This causes issues as there are hundreds of thousands of combinations.

I’m not sure how to proceed further. I really like ERPNext but am not sure how to get started with this fundamental aspect of operations. Your help and advice would be much appreciated.

Regards,

Shaheed

2

Wow, this is complex. I’ve read it twice and I’m still trying to get it down, so forgive me if I say something stupid. But, I’ll try to help you when I can. I’m going to try to break this down.

Types of Product

  • VMP - Generic Name
  • VMPP - Generic Name with Package Size
  • AMP - Branded Product
  • AMPP - Branded Product with Package Size

Codes:
DMD Code: unique government codes for every product
PIP Code: a more generic code
Barcode: self explanatory.

So, it seems to me that you should make the DMD Code the item code, and the VMP, AMP, VMPP, or AMPP the item name, as applicable. then have a checkbox in the item master that indicates whether the item is a generic or brand name item. Also, create a data field for PIP Code. And just use the barcode as is.

1.Every product should link back to the relevant DMD code.
Using the DMD Code as item code should work for this.

2.All generic products should be tracked separately to branded products.
The checkbox that I mentioned earlier should work.

3.I want to be able to use a barcode scanner to manage stock in the warehouse.
Already built into the system

4.Where there is more than one manufacturer for the product, I want to have a master product to which all the other manufacturer products are linked.
Do these fall under the same DMD codes? If so, there is a place for Manufacturer PN. If not, using item groups to link these may be your best bet.

5.Sales and purchase history should be able to be tracked across all the different levels of product in the hierarchy.
I believe you can track sales and purchase history for item groups, so that may be your best bet.

1 Like
3

@cpurbaugh: Thank you ever so much for spending so much time in trying to help me. I never thought about organising the hierarchy as you have suggested. I will experiment and see whether this works.