Supermarket Purchase Entry & Accounting Flow Table Explanations
1. PurchaseEntry Purpose: Stores the main purchase invoice header. Key Fields: purchase_entry_no, supplier_invoice,
invoice_date, gross_total, discount_amount, gst_amount, net_total, supplier_code Calculation: gross_total = sum of
(basic_price × purchase_qty) for all items net_total = gross_total - discount_amount + gst_amount + other_charges
+ round_off - cash_discount
2. PurchaseEntryDetails Purpose: Stores each product/batch in the purchase. Key Fields: purchase_entry_id,
product_id, batch_id, purchase_quantity, cost_price, gst_percentage, total_amount Calculation: total_amount =
(cost_price × purchase_quantity) + GST - discount
3. InventoryMaster Purpose: Tracks current stock for each product/batch. Key Fields: product_doc_id, batch_id, qoh
(quantity on hand) Calculation: qoh += purchase_quantity (on purchase) qoh -= sale_quantity (on sale)
4. InventoryTransactions Purpose: Logs every stock movement (purchase, sale, return, adjustment). Key Fields:
product_doc_id, batch_id, qty_change, transaction_type, reference_doc_id Calculation: qty_change = +ve for
purchase, -ve for sale/return
5. LedgerGroups Purpose: Main accounting groups (Asset, Liability, Income, Expense). Key Fields: group_name,
book_type, head_type
6. LedgerSubGroups Purpose: Sub-categories under each group. Key Fields: subgroup_name, subgroup_code,
group_id
7. LedgerMaster Purpose: Each account/party/product tracked in the ledger. Key Fields: ledger_code, ledger_name,
account_type, ledger_group_id, ledger_subgroup_id
8. LedgerBalances Purpose: Current balance for each ledger. Key Fields: ledger_id, balance_amount, balance_type
9. LedgerTransactions Purpose: Every financial transaction (double-entry: Dr/Cr). Key Fields: transaction_date,
voucher_type, ledger_id, amount, dr_cr, narration
10. Suppliers Table Purpose: Stores supplier info and financials. Key Fields: supplier_name, opening_balance,
credit_limit, debit_amount, credit_amount, closing_balance Calculation: closing_balance = opening_balance +
debit_amount - credit_amount Example SQL Insert Queries:
PurchaseEntry
INSERT INTO PurchaseEntry ( purchase_entry_doc_id, purchase_entry_no, supplier_invoice, invoice_date, entry_date,
pay_mode, tax_type, gross_total, discount_amount, gst_amount, cash_discount, other_charges, round_off, net_total,
supplier_code ) VALUES ( 'PE20250701-001', 'PE-001', 'INV-12345', '2025-07-01', '2025-07-01', 'CASH', 'GST', 845.00,
0.00, 152.10, 0.00, 0.00, 0.00, 997.10, 'SUP-001' ); PurchaseEntryDetails INSERT INTO PurchaseEntryDetails (
purchase_entry_detail_doc_id, purchase_entry_id, batch_id, product_id, purchase_quantity, cost_price, selling_price,
gst_percentage, total_amount, sku_description, sku_code ) VALUES ( 'PED20250701-001', 'PE20250701-001',
'BATCH001', 75138022398, 30, 28, 35, 18, 840.00, 'Good Day Cashew 200g', 'GD200' ); InventoryMaster INSERT INTO
InventoryMaster ( inventory_doc_id, product_doc_id, batch_id, expiry_date, location_code, qoh ) VALUES ( 'INV-GD200-
001', 75138022398, 'BATCH001', '2025-12-31', 'STORE-01', 30 ); InventoryTransactions INSERT INTO
InventoryTransactions ( inventory_txn_doc_id, product_doc_id, batch_id, qty_change, transaction_type,
reference_doc_id, location_code ) VALUES ( 'ITXN20250701-001', 75138022398, 'BATCH001', 30, 'PURCHASE',
'PE20250701-001', 'STORE-01' ); LedgerGroups INSERT INTO LedgerGroups ( ledger_group_doc_id, group_name,
book_type, head_type ) VALUES ('LG-INV', 'Inventory', 'Purchase Book', 'Asset'), ('LG-SUP', 'Payables', 'Purchase Book',
'Liability'); LedgerSubGroups INSERT INTO LedgerSubGroups ( ledger_subgroup_doc_id, subgroup_code,
subgroup_name, group_id ) VALUES ('LSG-INV-SKU', 'INV-SKU', 'SKU Inventory', 'LG-INV'), ('LSG-SUP', 'SUP',
'Supplier Payables', 'LG-SUP'); LedgerMaster INSERT INTO LedgerMaster ( ledger_doc_id, account_type, ledger_code,
ledger_name, opening_balance, opening_type, ledger_group_id, ledger_subgroup_id ) VALUES ('LED-INV-GD200',
'Asset', 'INV-GD200', 'Good Day Cashew Inventory', 0.0, 'Dr', 'LG-INV', 'LSG-INV-SKU'), ('LED-SUP-001', 'Liability',
'SUP-001', 'Britannia Industries Ltd.', 0.0, 'Cr', 'LG-SUP', 'LSG-SUP'); LedgerBalances INSERT INTO LedgerBalances (
ledger_balance_doc_id, ledger_id, balance_amount, balance_type ) VALUES ('LB-INV-GD200', 'LED-INV-GD200', 840.00,
'Dr'), ('LB-SUP-001', 'LED-SUP-001', 840.00, 'Cr'); LedgerTransactions INSERT INTO LedgerTransactions (
ledger_txn_doc_id, transaction_date, voucher_type, voucher_number, ledger_id, amount, dr_cr, narration ) VALUES
('LT-20250701-001', '2025-07-01', 'Purchase', 'PE-001', 'LED-INV-GD200', 840.00, 'Dr', 'Purchase Good Day Cashew'),
('LT-20250701-002', '2025-07-01', 'Purchase', 'PE-001', 'LED-SUP-001', 840.00, 'Cr', 'Payable to Supplier'); Suppliers
Table
UPDATE Suppliers SET credit_amount = credit_amount + 840.00, closing_balance = opening_balance + debit_amount -
(credit_amount + 840.00) WHERE supplier_id = '100000000021';
How Calculations Work Gross Total: gross_total = SUM(cost_price × purchase_quantity) for all items GST Amount:
gst_amount = SUM((cost_price × gst_percentage / 100) × purchase_quantity) Net Total: net_total = gross_total -
discount_amount + gst_amount + other_charges + round_off - cash_discount Inventory QOH: qoh = previous qoh +
purchase_quantity Ledger Balances: Inventory ledger increases (Dr) by purchase amount Supplier ledger increases (Cr)
by purchase amount Summary Table Table Purpose/Role Example Calculation/Entry PurchaseEntry Purchase bill
header gross_total, gst_amount, net_total PurchaseEntryDetails Each product/batch in bill cost_price × qty, gst,
total_amount InventoryMaster Current stock per product/batch qoh += purchase_qty InventoryTransactions Every
stock movement qty_change = +30 (purchase) LedgerGroups Main accounting group Inventory (Asset), Payables
(Liability) LedgerSubGroups Sub-categories for detailed tracking SKU Inventory, Supplier Payables LedgerMaster Each
account/party/product Good Day Inventory, Supplier Britannia LedgerBalances Current balance for each ledger
balance_amount += purchase LedgerTransactions Every Dr/Cr entry Inventory Dr, Supplier Cr Suppliers Supplier info
and financials credit_amount += purchase, closing_balance This structure ensures every purchase is traceable,
inventory is accurate, and accounts are always up-to-date—exactly as required in a modern supermarket
POS/accounting system.
