Project

General

Profile

BATCHID

User documentation
07/10/2025

  • Requirement Overview

We are currently in the process of developing a manual batch feature within the pharmaceutical system. This feature requires the entry of batch numbers, manufacturing dates, and expiry dates for medicines into the system. We will manage all reports and inventory movements accordingly. To facilitate this process, we are introducing a unique identifier called 'Batch ID,' which will track each stock inflow and outflow transaction.

Database Changes

  • Introduce a new table named 'BATCHPRICE_MASTER_DETAIL' containing the following columns:
    • BATCHID VARCHAR
    • BATCHCODE VARCHAR
    • COSTPRICE numeric(31,12)
    • LANDINGCOST
    • SUPCODE VARCHAR
    • STATUS TINYINT
    • MRP numeric(18,5)
    • SELLRATEBEFORETAX numeric (32,12)
    • W_SELLRATEBEFORETAX numeric (32,12)
    • W_MRP numeric (32,12)
    • STAMP datetime
    • COMPANYID VARCHAR
    • MFGDATE
    • EXPDATE
  • Include a new column 'NBATCHID' in all prod tables related to stock inflow and outflow. Retain the existing 'BATCHID' column unchanged, and ensure that data is sent in both columns accordingly.
  • Introduce the setting 'EnableBatchIDWiseBatchListing' with a binary choice (1 or 0). Set it to 1 if we need to use batch details(Batchcode, Cost Price, ExpDate, SELLRATEBEFORETAX) from the 'batchprice_master_detail' table, otherwise set it to 0 to use batch details() from ‘Batchprice_master’ table ’.
Note :
  • In the 'batchprice_master_detail' table, there exists a one-to-many relationship between the 'Batchcode' field of the 'batchprice_master' table and the 'Batchid' field of the batchprice_master_detail' table. This means that a single 'Batchcode' may correspond to multiple 'Batchid' entries. However, within the 'batchprice_master' table, each 'Batchcode' for the same product exists only once.
Limitations
  • We will only list the batch as per the t

UI design

Functional Requirement

Stock In

Requirement 1 :
Add the 'batchprice_master_detail' table in the database and add the new column 'NBATCHID' in the 'PURPROD', 'INVPROD', and 'OPPROD',’TRNPROD’,’ABBPROD’ tables. And create the setting called ‘EnableBatchIDWiseBatchListing’
*(Note: Set to 1 will list the batch based on batch ID (Batchprice_Master_Detail), while setting it to 0 will list batches based on batch code (Batchprice_Master).) *

Requirement 2 : Create a unique batch ID upon saving the following:

  • Purchase Voucher(PI)
    • Every product must generate the unique batch id
  • Opening Stock Entry
    • Opening stock Entry (Manual)
      • Generate the Unique Batch ID in each product
      • Opening Stock Upload
        When uploading the opening stock the if batch ID column in the upload sheet is left blank the system will automatically generate a new batch ID at the time of upload and if data have been given with batch id then the system will then verify the entered batch ID against registered batch IDs. If a listed batch ID is not registered in the system,then data should be validated and uploaded data will be reverted .
      • Opening Stock Carry Forward
        Opening stock carry forward will be carried down based on registered batch and batchid. If batch or batch id is not registered then carry forward should be reverted with a validation message.

Requirement 3 :
For the implementation of this new feature, both the 'NBATCHID' column and the existing 'BATCHID' column in all production tables should be used simultaneously. This is necessary because the processing and reporting of Batchid for new manual batch users will rely on the 'NBATCHID' column, whereas the 'BATCHID' column will continue to be used for compatibility purposes, catering to older reports and users.

Requirement 4 :
  • ‘EnableBatchIDWiseBatchListing’ setting will control the batch pop up in the system. If enabled ‘EnableBatchIDWiseBatchListing’=1 than it should generate the batch pop up in the sales and inventory movement forms based on table 'batchprice_master_detail' if disabled then batch pop should be generated based on Batch from ‘batchprice_master’.

Stock Out

Requirement 6 : Selected batch Batchidwise stock deducted in fifo expiry date wise descending order.

Batchlisting
Purchase /Opening stock
Active batch all that are available

Movement

  1. Only stock avaliable
    1. Batch, exp date, mrp

Definition of Done

  • Users should have the capability to generate a unique batch ID for each purchase or opening stock entry. The data must be saved in the 'NBATCHID' column within all prod tables, as well as in the 'batchprice_master_detail' and 'batchprice_master' tables.
  • Users should be able to seamlessly execute purchase invoices, opening stock entries, and stock sales, with each transaction tracked using a unique batch ID.

Files