How to set up medicines and stores for a StopTB van that is already configured, bring them to the camp laptop, and confirm every link with read-only queries.

1. What this covers

The van configuration (zone, hub, van, service point, operator user) is already done by the L2 team. This page covers what comes after it: the medicine masters, the stores, the store mappings, the laptop download, and the checks that prove the van will show medicines in the StopTB app.

Who

Does

Where

L2 support (logged in with the state's provider admin login, e.g. AdminSample)

UOM, category, items, stores, store mapping, item mapping; then runs the check queries (SELECT only)

Admin portal → Inventory → Master on central; queries on the central DB and the laptop DB

Camp operator (e.g. sample_op1)

Download Masters, Physical Stock Entry, Data Sync Upload

Camp laptop (MMU UI + Inventory UI)

Tab nurse / registrar

Registration and OPD; medicines are given automatically

StopTB tab app, connected to the laptop

Sample values: all IDs and names on this page are examples, not real data: provider line 1001 (Sample State, MMU), hub 11, vans 101 and 102, operator sample_op1, sub store Sample Van1 Camp. Replace them with your own before running a query.

Every query on this page is read-only. SET @van = … only creates a variable in your own Workbench session. Nothing is inserted, updated or deleted.

2. How a van gets its medicines

There is no table that links a van directly to medicines. The link always goes through the van's own store. One laptop serves one van, and that van has one camp sub-store.

Provider line (state + MMU, e.g. 1001 Sample State MMU)
 ├── UOM, Item Category, Facility Type, Item Master       (created once per state)
 │
 └── Hub (parking place, e.g. 11)
       └── Main store            IsMainFacility = 1       m_parkingplace.FacilityID → main store
             ├── Sub store A     IsMainFacility = 0, parent = main store
             │     ├── van 101   m_van.FacilityID → sub store A
             │     └── items     m_itemfacilitymapping (sub store A + item)
             └── Sub store B     IsMainFacility = 0, parent = main store
                   ├── van 102   m_van.FacilityID → sub store B
                   └── items     m_itemfacilitymapping (sub store B + item)

App drug list = items mapped to the van's sub store (view v_drugforprescription)

A medicine appears in the app's dropdown only when all of these are true:

  1. The van is mapped to a store (m_van.FacilityID is filled).
  2. The medicine is mapped to that store (m_itemfacilitymapping, not deleted).
  3. The item and the mapping are on the same provider line.
  4. The item has a Drug Form and is not deleted.
  5. The item has no batches yet (shows with quantity 0), or at least one batch that has not expired. If every batch has expired, the medicine is hidden.
  6. In the app, the nurse picks the matching Drug Form first (Tablet, Capsule, Syrup …). The medicine list is filtered by that form.

3. Before you start: van check

Confirm the van configuration is complete before touching inventory. Replace 101 with your van ID. Run on central.

Central · van configuration
SET @van = 101;
SELECT v.VanID, v.VanName, v.ProviderServiceMapID AS line,
       p.ParkingPlaceID AS hub_id, p.ParkingPlaceName AS hub,
       (SELECT GROUP_CONCAT(DISTINCT ServicePointID) FROM db_iemr.m_vanservicepointmap
         WHERE VanID = v.VanID AND Deleted = 0) AS service_point,
       (SELECT GROUP_CONCAT(DISTINCT u.UserName) FROM db_iemr.m_uservanmapping uv
          JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID = uv.UserParkingPlaceMapID AND up.Deleted = 0
          JOIN db_iemr.m_user u ON u.UserID = up.UserID
         WHERE uv.VanID = v.VanID AND uv.Deleted = 0) AS operator
FROM db_iemr.m_van v
LEFT JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID = v.ParkingPlaceID
WHERE v.VanID = @van AND v.Deleted = 0;

Expected: one row with the line, hub, service point and the operator's username all filled. If any is empty, finish the van configuration first.

To find a van ID from its name:

Central · find vans of a state
SELECT v.VanID, v.VanName, v.ProviderServiceMapID AS line, pp.ParkingPlaceName AS hub,
       v.FacilityID AS store_id, f.FacilityName AS store
FROM db_iemr.m_van v
JOIN db_iemr.m_providerservicemapping p ON p.ProviderServiceMapID = v.ProviderServiceMapID
JOIN db_iemr.m_state st                 ON st.StateID = p.StateID
LEFT JOIN db_iemr.m_parkingplace pp     ON pp.ParkingPlaceID = v.ParkingPlaceID
LEFT JOIN db_iemr.m_facility f          ON f.FacilityID = v.FacilityID
WHERE v.Deleted = 0 AND st.StateName LIKE 'Sample State%'
ORDER BY v.VanID;

4. Which masters belong to the state

Each provider line keeps its own inventory masters. Another state's UOMs or items are not visible to this state, so a new state starts empty. A few masters are shared by everyone and must not be created again.

Master

Table

Scope

Admin creates it?

Unit of measure

m_uom

Per provider line

YES

Item category (+ issue method)

m_itemcategory

Per provider line

YES

Facility type

m_facilitytype

Per provider line

YES

Item (medicine)

m_item

Per provider line

YES

Stores

m_facility

Per provider line

YES

Item to store mapping

m_itemfacilitymapping

Per store

YES

Drug form (Tablet, Syrup …)

m_itemform

Shared by all

NO, EXISTS

Route (Oral …)

m_routeofadmin

Shared by all

NO, EXISTS

Frequency, duration, dose

m_drugfrequency, m_drugduration, m_drugdose

Shared by all

NO, EXISTS

Log in with the state's own provider admin login and pick State = the state, Service = MMU on every screen. A master saved under another line (another state, or the Stop TB line) will not show for this state's vans.

Find the provider line of a state:

Central · provider lines of a state
SELECT p.ProviderServiceMapID AS line, sp.ServiceProviderName AS provider, s.ServiceName, st.StateName
FROM db_iemr.m_providerservicemapping p
JOIN db_iemr.m_state st                ON st.StateID = p.StateID
LEFT JOIN db_iemr.m_servicemaster s    ON s.ServiceID = p.ServiceID
LEFT JOIN db_iemr.m_serviceprovider sp ON sp.ServiceProviderID = p.ServiceProviderID
WHERE st.StateName LIKE 'Sample State%' AND p.Deleted = 0;

Use the MMU line for all inventory masters and for the camp operator. The Stop TB line is only for the tab nurse and registrar.

5. Admin portal steps, screen by screen

All screens are under Admin portal → Inventory → Master. Do them in this order: each one uses what the one before it created.

Every screen works the same way.

  1. At the top, choose State = your state and Serviceline = MMU. The table shows what already exists.
  2. Click the screen's create button (for example Add UOM).
  3. Fill the fields and click add (the + icon). The row goes into a list below the form. You can add several rows.
  4. Click Save. Nothing is stored until you click Save.

To change a saved row, use the edit icon in the table, then Update.

5.1 · Unit of Measure Master

Why: every medicine's strength needs a unit. Item Master cannot be saved without one.

  1. Choose State and Serviceline (MMU), then click Add UOM.
  2. UOM Code (required, must be unique), UOM Name (required), UOM Description (optional).
  3. Click add for each unit, then Save.

UOM Code

UOM Name

Used for

MG

mg

Tablets, capsules (500 mg)

ML

ml

Syrups (5 ml)

MCG

mcg

Small-dose tablets

PCT

%

Creams, ointments (1 %)

GM

gm

Powders, ORS

Check · UOM of the van's line
SET @van = 101;
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = @van);
SELECT UOMID, UOMName, UOMCode, CreatedBy, CreatedDate
FROM db_iemr.m_uom WHERE ProviderServiceMapID = @line AND Deleted = 0 ORDER BY UOMName;
-- expected: one row per unit you saved

5.2 · Item Category Master

Why: every medicine belongs to a category, and the category decides which batch is given first.

  1. Choose State and Serviceline (MMU), then click Add Category.
  2. Category Code (e.g. DRUG), Category Name (e.g. Drug), Category Description.
  3. Click add, then Save.

5.3 · Item Issue Method Config

Why: when the nurse submits an OPD, the laptop gives medicine from batches in this order.

  1. Choose State and Serviceline (MMU), then click Create Item Config.
  2. Item Category Name = Drug, Issue Type = First Expiry First Out.
  3. Click add, then Save.

Issue Type

Batch given first

First Expiry First Out (recommended)

The batch that expires soonest

First In First Out

The batch entered first

Not set

The laptop uses First Expiry First Out

In every case the laptop skips a batch that expires before the prescribed course ends.

Check · categories and issue type
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = 101);
SELECT ItemCategoryID, ItemCategoryName, IssueType
FROM db_iemr.m_itemcategory WHERE ProviderServiceMapID = @line AND Deleted = 0;
-- expected: Drug | First Expiry First Out

5.4 · Item Master

Why: these are the medicines. They are created once per state, and every van of the state uses the same list.

  1. Choose State and Serviceline (MMU), then click Add Item.
  2. Fill the fields below, click add, and repeat for each medicine. Then Save.

Field

What to enter

Item Code, Item Name

Unique code and the name the nurse will see, e.g. PCM500 / Paracetamol

Medical / Non-Medical

Medical

Item Form

Required for the app. Tablet, Capsule, Syrup … The nurse picks the form first, then sees only medicines of that form.

Strength + UOM

e.g. 500 + mg (UOM from 5.1)

Item Category

Drug (from 5.2)

Route Of Admin

Oral, Local application …

Composition

Generic name, e.g. Paracetamol

Scheduled / Non-Scheduled

As per the drug

Pharmacology Category, Manufacturer, Snomed CT, Drug List Type, Item Description

Optional

Min Stock (low stock alert)

Only on the newer Admin build. Optional.

Check · items of the van's line
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = 101);
SELECT i.ItemID, i.ItemCode, i.ItemName, f.ItemFormName AS Form, i.Strength, u.UOMName AS UOM,
       c.ItemCategoryName AS Category, r.RouteName AS Route
FROM db_iemr.m_item i
LEFT JOIN db_iemr.m_itemform f     ON f.ItemFormID     = i.ItemFormID
LEFT JOIN db_iemr.m_uom u          ON u.UOMID          = i.UOMID
LEFT JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID = i.ItemCategoryID
LEFT JOIN db_iemr.m_routeofadmin r ON r.RouteID        = i.RouteID
WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0
ORDER BY i.ItemName;
-- every row needs Form, UOM and Category

5.5 · Facility Type Master

Why: every store needs a type.

  1. Choose State and Serviceline (MMU), then click Add Facility Type.
  2. Facility Type Code, Facility Type Name, Facility Description. Create two: Main (for hub stores) and Camp (for van stores). One type for both also works.
  3. Click add, then Save.

5.6 · Main Store & Sub Store

Why: stock is held in stores. Each hub has one main store; each van has its own sub store under it.

  1. Choose State and Serviceline (MMU), then click Create Store.
  2. For the hub: Facility Type, Facility Code, Facility Name (e.g. Sample Hub Main), Location, Physical Location, select Main Store. Click add.
  3. For each van: same fields, Facility Name after the van (e.g. Sample Van1 Camp), select Sub Store, then pick its Main Store (the hub's main store). Click add.
  4. Click Save.

A van must get a Sub Store, never a Main Store. Inventory on the laptop lists a van's store only as a sub store under its main store, and finds the van from that sub store.

Check · stores of the line
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = 101);
SELECT f.FacilityID, f.FacilityName, f.IsMainFacility + 0 AS is_main, f.MainFacilityID AS parent,
       (SELECT GROUP_CONCAT(VanID) FROM db_iemr.m_van WHERE FacilityID = f.FacilityID AND Deleted = 0)                   AS linked_vans,
       (SELECT GROUP_CONCAT(ParkingPlaceID) FROM db_iemr.m_parkingplace WHERE FacilityID = f.FacilityID AND Deleted = 0) AS linked_hubs,
       (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m WHERE m.FacilityID = f.FacilityID AND m.Deleted = 0)      AS items
FROM db_iemr.m_facility f
WHERE f.ProviderServiceMapID = @line AND f.Deleted = 0
ORDER BY f.FacilityID;

Expected after 5.7 and 5.8: each main store has is_main = 1 and its hub in linked_hubs; each sub store has is_main = 0, the main store as parent, its van in linked_vans, and items above 0.

5.7 · Van/Parking Place to Store Mapping

Why: this tells the system which store belongs to which hub and which van. Without it the van has no medicines.

  1. Choose State and Serviceline (MMU), then click Map Store.
  2. Hub: select Main Store, pick the main store and the Parking Place. Click add.
  3. Each van: select Sub Store, pick the main store, then the sub store, then the Van. Click add. The van list shows only vans of that main store's hub that have no store yet.
  4. Click Save.

On some service lines the screen says Hub and Spoke instead of Parking Place and Van.

5.8 · Item To Store Mapping

Why: the medicines mapped to the van's sub store are exactly the list the nurse sees in the app.

  1. Choose State and Serviceline (MMU), then click Add Mapping.
  2. Select Sub Store, pick the Main Store, then the van's Sub Store.
  3. Pick the Item Category (Drug), then either Individual Mapping (tick each medicine under Item Name) or Bulk Mapping (all medicines of that category).
  4. Click add, then Save. Repeat for each van's sub store.

Mapping the medicines to the main store as well is optional; it is used for refills.

Not needed for StopTB dispensing: Supplier Master, Manufacturer Master, Pharmacological Category Master, Expiry Date Alert Config, Item To Facility Mapping.

One check for all masters of the van's line:

Central · masters summary
SET @van = 101;
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = @van);
SELECT 1 AS step, 'UOM' AS master, COUNT(*) AS total_rows, GROUP_CONCAT(UOMName ORDER BY UOMName SEPARATOR ', ') AS detail
  FROM db_iemr.m_uom WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 2, 'Item category', COUNT(*), GROUP_CONCAT(CONCAT(ItemCategoryName,' (',IFNULL(IssueType,'no issue type'),')') SEPARATOR ', ')
  FROM db_iemr.m_itemcategory WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 3, 'Facility type', COUNT(*), GROUP_CONCAT(FacilityTypeName SEPARATOR ', ')
  FROM db_iemr.m_facilitytype WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 4, 'Items', COUNT(*), CONCAT(SUM(ItemFormID IS NULL),' without form, ',SUM(UOMID IS NULL),' without UOM, ',SUM(ItemCategoryID IS NULL),' without category')
  FROM db_iemr.m_item WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 5, 'Items using UOM of another line', COUNT(*), GROUP_CONCAT(i.ItemName SEPARATOR ', ')
  FROM db_iemr.m_item i JOIN db_iemr.m_uom u ON u.UOMID = i.UOMID
  WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0 AND u.ProviderServiceMapID <> @line
UNION ALL SELECT 6, 'Items using category of another line', COUNT(*), GROUP_CONCAT(i.ItemName SEPARATOR ', ')
  FROM db_iemr.m_item i JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID = i.ItemCategoryID
  WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0 AND c.ProviderServiceMapID <> @line
UNION ALL SELECT 7, 'Stores', COUNT(*), GROUP_CONCAT(CONCAT(FacilityID,' ',FacilityName,IF(IsMainFacility+0=1,' [main]',CONCAT(' [sub of ',IFNULL(MainFacilityID,'?'),']'))) SEPARATOR ', ')
  FROM db_iemr.m_facility WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 8, 'Item-store mappings', COUNT(*), CONCAT(COUNT(DISTINCT FacilityID),' stores, ',COUNT(DISTINCT ItemID),' items')
  FROM db_iemr.m_itemfacilitymapping WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 9, 'Items not mapped to any store', COUNT(*), GROUP_CONCAT(i.ItemName SEPARATOR ', ')
  FROM db_iemr.m_item i WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0
  AND NOT EXISTS (SELECT 1 FROM db_iemr.m_itemfacilitymapping m WHERE m.ItemID = i.ItemID AND m.Deleted = 0);

Step

Good result

1–3

More than 0 rows; category shows First Expiry First Out (or no issue type, which the laptop treats as First Expiry First Out)

4

More than 0 items, and 0 without form / UOM / category

5, 6

0 (nothing borrowed from another state)

7

Main stores shown as "main", and one store shown as "sub of <main store ID>" per van

8, 9

Mappings cover every van's sub store; 9 is 0 or only items left out on purpose

6. Users for the van

User

Line

Roles

Mapping

Uses

Camp operator (one per van)

MMU

Pharmacist and Data Sync (others optional)

Hub → this one van

Laptop: MMU UI and Inventory UI with the same login

Nurse

Stop TB

Nurse

Team VanID = the van

Tab app: OPD

Registrar

Stop TB

Registrar

Team VanID = the van

Tab app: registration

No separate inventory user is created. The Inventory UI login checks two things:

  1. The user has a Pharmacist role (role order does not matter).
  2. The user's Designation is the name of one of the user's mapped role screens. A designation of Nurse is fine when the user also has the Nurse role.
Central · operator roles and designation
SELECT u.UserName, d.DesignationName, r.RoleName, m.ProviderServiceMapID AS line, m.USRMappingID
FROM db_iemr.m_user u
LEFT JOIN db_iemr.m_designation d       ON d.DesignationID = u.DesignationID
JOIN db_iemr.m_userservicerolemapping m ON m.UserID = u.UserID AND m.Deleted = 0
JOIN db_iemr.m_role r                   ON r.RoleID = m.RoleID
WHERE u.UserName = 'sample_op1'
ORDER BY m.USRMappingID;
Central · role screens mapped for the line
SELECT r.RoleName, s.ScreenName
FROM db_iemr.m_servicerolescreenmapping srs
JOIN db_iemr.m_role r   ON r.RoleID   = srs.RoleID
JOIN db_iemr.m_screen s ON s.ScreenID = srs.ScreenID
WHERE srs.ProviderServiceMapID = 1001 AND srs.Deleted = 0
  AND r.RoleName IN ('Pharmacist', 'Nurse', 'Data Sync')
ORDER BY r.RoleName;

The tab users' team VanID is stored in m_userservicerolemapping.VanID, added by AMRIT-DB V128. If a query on this column fails with Unknown column 'VanID', central has not run V128 yet: raise it with DevOps.

7. Full check for one van

Run on central after the admin finishes. Each row reports OK or MISSING. Change only the van ID.

Central · complete van inventory check
SET @van = 101;
SELECT x.step, x.check_name, x.value, IF(x.ok, 'OK', 'MISSING / WRONG') AS status
FROM (
  SELECT 1 step, 'Van, line' check_name, (SELECT CONCAT(VanID,' ',VanName,' / line ',ProviderServiceMapID) FROM db_iemr.m_van WHERE VanID=@van AND Deleted=0) value, EXISTS(SELECT 1 FROM db_iemr.m_van WHERE VanID=@van AND Deleted=0) ok
  UNION ALL SELECT 2, 'Hub', (SELECT CONCAT(p.ParkingPlaceID,' ',p.ParkingPlaceName) FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van)
  UNION ALL SELECT 3, 'Service point', (SELECT GROUP_CONCAT(DISTINCT ServicePointID) FROM db_iemr.m_vanservicepointmap WHERE VanID=@van AND Deleted=0), EXISTS(SELECT 1 FROM db_iemr.m_vanservicepointmap WHERE VanID=@van AND Deleted=0)
  UNION ALL SELECT 4, 'Hub main store', (SELECT CONCAT(f.FacilityID,' ',f.FacilityName) FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID JOIN db_iemr.m_facility f ON f.FacilityID=p.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID JOIN db_iemr.m_facility f ON f.FacilityID=p.FacilityID AND f.IsMainFacility=1 WHERE v.VanID=@van)
  UNION ALL SELECT 5, 'Van sub store (Map Store)', (SELECT CONCAT(f.FacilityID,' ',f.FacilityName) FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID AND f.Deleted=0 WHERE v.VanID=@van)
  UNION ALL SELECT 6, 'Sub store: Is Main = No', (SELECT f.IsMainFacility+0 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID AND f.IsMainFacility=0 WHERE v.VanID=@van)
  UNION ALL SELECT 7, 'Sub store parent = hub main store', (SELECT CONCAT('parent ',IFNULL(f.MainFacilityID,'none'),' / hub store ',IFNULL(p.FacilityID,'none')) FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van AND f.MainFacilityID=p.FacilityID)
  UNION ALL SELECT 8, 'Sub store line = van line', (SELECT CONCAT(f.ProviderServiceMapID,' / ',v.ProviderServiceMapID) FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van AND f.ProviderServiceMapID=v.ProviderServiceMapID)
  UNION ALL SELECT 9, 'Items mapped to sub store', (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID WHERE v.VanID=@van AND m.Deleted=0), (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID WHERE v.VanID=@van AND m.Deleted=0) > 0
  UNION ALL SELECT 10, 'Drug list rows (what the app gets)', (SELECT COUNT(*) FROM db_iemr.v_drugforprescription d JOIN db_iemr.m_van v ON v.FacilityID=d.facilityID WHERE v.VanID=@van), (SELECT COUNT(*) FROM db_iemr.v_drugforprescription d JOIN db_iemr.m_van v ON v.FacilityID=d.facilityID WHERE v.VanID=@van) > 0
  UNION ALL SELECT 11, 'Drug category issue type (FEFO)', (SELECT GROUP_CONCAT(DISTINCT CONCAT(c.ItemCategoryName,': ',IFNULL(c.IssueType,'not set = FEFO'))) FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID JOIN db_iemr.m_item i ON i.ItemID=m.ItemID JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID=i.ItemCategoryID WHERE v.VanID=@van AND m.Deleted=0), NOT EXISTS(SELECT 1 FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID JOIN db_iemr.m_item i ON i.ItemID=m.ItemID JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID=i.ItemCategoryID WHERE v.VanID=@van AND m.Deleted=0 AND IFNULL(c.IssueType,'First Expiry First Out') NOT LIKE 'First Expiry%') AND EXISTS(SELECT 1 FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID WHERE v.VanID=@van AND m.Deleted=0)
  UNION ALL SELECT 12, 'Operator mapped to van', (SELECT GROUP_CONCAT(DISTINCT u.UserName) FROM db_iemr.m_uservanmapping uv JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID=uv.UserParkingPlaceMapID AND up.Deleted=0 JOIN db_iemr.m_user u ON u.UserID=up.UserID WHERE uv.VanID=@van AND uv.Deleted=0), EXISTS(SELECT 1 FROM db_iemr.m_uservanmapping uv WHERE uv.VanID=@van AND uv.Deleted=0)
  UNION ALL SELECT 13, 'Operator has Pharmacist + Data Sync', (SELECT GROUP_CONCAT(DISTINCT CONCAT(u.UserName,': ',r.RoleName)) FROM db_iemr.m_uservanmapping uv JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID=uv.UserParkingPlaceMapID AND up.Deleted=0 JOIN db_iemr.m_user u ON u.UserID=up.UserID JOIN db_iemr.m_userservicerolemapping m ON m.UserID=u.UserID AND m.Deleted=0 JOIN db_iemr.m_role r ON r.RoleID=m.RoleID AND r.RoleName IN ('Pharmacist','Data Sync') WHERE uv.VanID=@van AND uv.Deleted=0), (SELECT COUNT(DISTINCT r.RoleName) FROM db_iemr.m_uservanmapping uv JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID=uv.UserParkingPlaceMapID AND up.Deleted=0 JOIN db_iemr.m_userservicerolemapping m ON m.UserID=up.UserID AND m.Deleted=0 JOIN db_iemr.m_role r ON r.RoleID=m.RoleID AND r.RoleName IN ('Pharmacist','Data Sync') WHERE uv.VanID=@van AND uv.Deleted=0) = 2
  UNION ALL SELECT 14, 'Stock in sub store (after laptop upload)', (SELECT CONCAT(COUNT(*),' batches / ',IFNULL(SUM(e.QuantityInHand),0),' in hand') FROM db_iemr.t_itemstockentry e JOIN db_iemr.m_van v ON v.FacilityID=e.FacilityID WHERE v.VanID=@van AND e.Deleted=0), TRUE
) x ORDER BY x.step;

Step

If MISSING / WRONG

1–3

Van configuration incomplete. Finish it first.

4

Main store not mapped to the hub (5.6, 5.7). Needed for refills.

5

Van not mapped to any store (5.7). The app shows no medicines.

6

The van's store was created as a Main Store. Create a Sub Store and map the van to it.

7, 8

Sub store under the wrong main store or wrong line.

9, 10

No items mapped to the van's sub store (5.8), or items lack a form.

11

Category set to First In First Out (5.3). Not set is OK: the laptop uses First Expiry First Out. MISSING also when step 9 is 0.

12, 13

Operator not mapped to the van, or missing Pharmacist / Data Sync.

14

Always OK. Stock is entered on the laptop and reaches central only after Upload.

8. Laptop: Download Masters

Everything above is done on central. The camp laptop receives it with Data Sync → Download Masters. Running it again later is safe and brings any later admin change.

Before downloading

Check

Must be

/opt/amrit/config/.env

STOPTB_ENFORCE_VANID=true, STOPTB_VAN_ID=<this laptop's van>, CENTRAL_URL = the same central the masters were created on

MySQL mode

SELECT @@GLOBAL.sql_mode; must not contain ONLY_FULL_GROUP_BY (otherwise the drug list API fails and the tab keeps an old list)

Services

Restarted after any .env change: sudo systemctl restart 'amrit-*'

Village download date

Not NULL (see query below). A NULL date makes the laptop request every village and overloads central.

Laptop · village download date
SELECT TableName, LastDownloadDate
FROM db_iemr.m_downloadmastertable
WHERE TableName = 'm_DistrictBranchMapping';
-- a date: fine. NULL: stop and escalate (AMRIT-DB V128 fills it on start-up).

Steps

  1. Open MMU on the laptop, log in as the camp operator.
  2. Data Sync → Download Masters. All masters should succeed. If a few fail on the first run, run it once more (hubs must arrive before vans). If every master fails quickly, log out, log in again and retry.
  3. Run the laptop check below.
  4. Open Inventory with the same login: Main Store = No → hub main store → the van's sub store, then Physical Stock Entry (batch, future expiry, quantity).
  5. On the tab, the nurse logs out and back in, opens a new General OPD, picks the Drug Form, and the medicines appear.
Laptop · after Download Masters
SET @van = 101;
SELECT v.VanID, v.FacilityID AS sub_store_id, f.FacilityName AS sub_store,
       f.IsMainFacility + 0 AS is_main, f.MainFacilityID AS parent,
       (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m WHERE m.FacilityID = v.FacilityID AND m.Deleted = 0) AS items_mapped,
       (SELECT COUNT(*) FROM db_iemr.v_drugforprescription d WHERE d.facilityID = v.FacilityID) AS drug_list
FROM db_iemr.m_van v
LEFT JOIN db_iemr.m_facility f ON f.FacilityID = v.FacilityID
WHERE v.VanID = @van;
-- must match central: same sub store, is_main 0, same parent, items_mapped = drug_list

What Download Masters changes

Tables

How

Stores, van, hub, items, UOM, category, facility type, item mapping, role mapping (service-line masters)

Downloaded in full every time on a StopTB laptop, inserted or updated by the same IDs as central

Users (designation etc.), shared masters such as villages

Only rows changed since the last download

Patients, visits, prescriptions, stock, sync flags

Never touched

Nothing is deleted on the laptop; rows deleted on central arrive with Deleted = 1. Central's version always wins for masters, so masters are changed on central only.

9. How inventory follows the van ID

Each laptop is fixed to one van by STOPTB_VAN_ID in .env. The laptop database holds every state's masters (they all come from central); the van ID decides which ones are used.

Part

Van taken from

Store used

Tab drug list (TM-API)

.env STOPTB_VAN_ID (the van the app sends is ignored)

m_van.FacilityID of that van → items mapped to it

Dispensing on OPD submit (FLW-API)

.env STOPTB_VAN_ID

Same sub store; batches given in the category issue order (default First Expiry First Out), skipping batches that expire before the course ends; refused if stock is short

Inventory UI (stock entry, adjustment)

The operator's choice at login

The sub store the operator picks; Inventory finds the van from it

Data Sync upload

Each row carries VanID + its own row ID

Central keeps every van's rows apart (VanSerialNo + VanID)

The operator must pick the sub store of the .env van in Inventory. If they pick another store, stock goes into store A while the nurse dispenses from store B.

Item IDs are the same on every laptop because items are created only on central. Stock is not shared: each batch row belongs to one store, so the same medicine on two laptops is two separate counts.

10. Problems and fixes

Symptom

Cause

Fix

Medicine dropdown empty in the app

Van has no store, or no items mapped to it (check steps 5, 9, 10)

Admin portal: sub store, map van, map items. Laptop: Download Masters. Tab: log out / in, new OPD.

Medicine field disabled or empty for one form

Drug Form not chosen, or no mapped item of that form

Pick the right form (Tablet, Capsule, Syrup …)

Tab shows another state's medicines

Old list kept on the tab after a failed refresh, or wrong STOPTB_VAN_ID

Check .env and sql_mode; nurse logs out / in and opens a new OPD

A medicine disappeared from the list

All its batches have expired

Enter a new batch with a later expiry (Physical Stock Entry)

Every OPD with medicines refused

No usable stock in the van's sub store, or a batch expires before the course ends

Physical Stock Entry with a far expiry date

Both hub stores created as Main

Wrong option in Main Store & Sub Store

Create a Sub Store under the hub's main store; leave the extra main store unused

Items mapped but app still empty

Items mapped to a store no van uses

Map them to the van's sub store (check: linked_vans of the store)

Inventory login: "Designation is not matched with your roles"

No Pharmacist role, or designation not among the user's role screens

Add the Pharmacist role, or set the designation to a role the user has

Inventory shows no store to pick

Store not a sub store of a main store, or on another line

Fix in Main Store & Sub Store (parent, line)

Download Masters: all fail within seconds

Central session expired

Log out, log in, retry

Download Masters: a few fail on first run

Hubs arrive after vans

Run Download Masters again

Query error "Unknown column 'VanID'"

Central is below AMRIT-DB V128

DevOps: deploy AMRIT-DB from main

Stock Adjustment shows 502 on the laptop

It calls the eAushadhi service, which is not on camp laptops

Expected; use Physical Stock Entry

11. Rules

  1. One laptop = one van = one sub store, with a unique STOPTB_VAN_ID per laptop.
  2. Create and change masters (items, stores, mappings, users) only on central, with the state's provider admin login. Never on a laptop.
  3. Laptops are set up from a backup of the same central they download from. A backup from another environment gives different IDs.
  4. Camp stock is entered only on the laptop. Never enter camp sub-store stock on central.
  5. Don't change expiry dates in the database. Add a new batch through Physical Stock Entry.
  6. Check queries are SELECT only. Every change is made through the Admin portal screens, never by SQL.

Source: AMRIT release-3.12.0 (MMU-API, TM-API, FLW-API, Inventory-UI, ADMIN-UI, AMRIT-DB). Queries tested on the StopTB test database.

How to set up medicines and stores for a StopTB van that is already configured, bring them to the camp laptop, and confirm every link with read-only queries.

1. What this covers

The van configuration (zone, hub, van, service point, operator user) is already done by the L2 team. This page covers what comes after it: the medicine masters, the stores, the store mappings, the laptop download, and the checks that prove the van will show medicines in the StopTB app.

Who

Does

Where

L2 support (logged in with the state's provider admin login, e.g. AdminSample)

UOM, category, items, stores, store mapping, item mapping; then runs the check queries (SELECT only)

Admin portal → Inventory → Master on central; queries on the central DB and the laptop DB

Camp operator (e.g. sample_op1)

Download Masters, Physical Stock Entry, Data Sync Upload

Camp laptop (MMU UI + Inventory UI)

Tab nurse / registrar

Registration and OPD; medicines are given automatically

StopTB tab app, connected to the laptop

Sample values: all IDs and names on this page are examples, not real data: provider line 1001 (Sample State, MMU), hub 11, vans 101 and 102, operator sample_op1, sub store Sample Van1 Camp. Replace them with your own before running a query.

Every query on this page is read-only. SET @van = … only creates a variable in your own Workbench session. Nothing is inserted, updated or deleted.

2. How a van gets its medicines

There is no table that links a van directly to medicines. The link always goes through the van's own store. One laptop serves one van, and that van has one camp sub-store.

Provider line (state + MMU, e.g. 1001 Sample State MMU)
 ├── UOM, Item Category, Facility Type, Item Master       (created once per state)
 │
 └── Hub (parking place, e.g. 11)
       └── Main store            IsMainFacility = 1       m_parkingplace.FacilityID → main store
             ├── Sub store A     IsMainFacility = 0, parent = main store
             │     ├── van 101   m_van.FacilityID → sub store A
             │     └── items     m_itemfacilitymapping (sub store A + item)
             └── Sub store B     IsMainFacility = 0, parent = main store
                   ├── van 102   m_van.FacilityID → sub store B
                   └── items     m_itemfacilitymapping (sub store B + item)

App drug list = items mapped to the van's sub store (view v_drugforprescription)

A medicine appears in the app's dropdown only when all of these are true:

  1. The van is mapped to a store (m_van.FacilityID is filled).
  2. The medicine is mapped to that store (m_itemfacilitymapping, not deleted).
  3. The item and the mapping are on the same provider line.
  4. The item has a Drug Form and is not deleted.
  5. The item has no batches yet (shows with quantity 0), or at least one batch that has not expired. If every batch has expired, the medicine is hidden.
  6. In the app, the nurse picks the matching Drug Form first (Tablet, Capsule, Syrup …). The medicine list is filtered by that form.

3. Before you start: van check

Confirm the van configuration is complete before touching inventory. Replace 101 with your van ID. Run on central.

Central · van configuration
SET @van = 101;
SELECT v.VanID, v.VanName, v.ProviderServiceMapID AS line,
       p.ParkingPlaceID AS hub_id, p.ParkingPlaceName AS hub,
       (SELECT GROUP_CONCAT(DISTINCT ServicePointID) FROM db_iemr.m_vanservicepointmap
         WHERE VanID = v.VanID AND Deleted = 0) AS service_point,
       (SELECT GROUP_CONCAT(DISTINCT u.UserName) FROM db_iemr.m_uservanmapping uv
          JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID = uv.UserParkingPlaceMapID AND up.Deleted = 0
          JOIN db_iemr.m_user u ON u.UserID = up.UserID
         WHERE uv.VanID = v.VanID AND uv.Deleted = 0) AS operator
FROM db_iemr.m_van v
LEFT JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID = v.ParkingPlaceID
WHERE v.VanID = @van AND v.Deleted = 0;

Expected: one row with the line, hub, service point and the operator's username all filled. If any is empty, finish the van configuration first.

To find a van ID from its name:

Central · find vans of a state
SELECT v.VanID, v.VanName, v.ProviderServiceMapID AS line, pp.ParkingPlaceName AS hub,
       v.FacilityID AS store_id, f.FacilityName AS store
FROM db_iemr.m_van v
JOIN db_iemr.m_providerservicemapping p ON p.ProviderServiceMapID = v.ProviderServiceMapID
JOIN db_iemr.m_state st                 ON st.StateID = p.StateID
LEFT JOIN db_iemr.m_parkingplace pp     ON pp.ParkingPlaceID = v.ParkingPlaceID
LEFT JOIN db_iemr.m_facility f          ON f.FacilityID = v.FacilityID
WHERE v.Deleted = 0 AND st.StateName LIKE 'Sample State%'
ORDER BY v.VanID;

4. Which masters belong to the state

Each provider line keeps its own inventory masters. Another state's UOMs or items are not visible to this state, so a new state starts empty. A few masters are shared by everyone and must not be created again.

Master

Table

Scope

Admin creates it?

Unit of measure

m_uom

Per provider line

YES

Item category (+ issue method)

m_itemcategory

Per provider line

YES

Facility type

m_facilitytype

Per provider line

YES

Item (medicine)

m_item

Per provider line

YES

Stores

m_facility

Per provider line

YES

Item to store mapping

m_itemfacilitymapping

Per store

YES

Drug form (Tablet, Syrup …)

m_itemform

Shared by all

NO, EXISTS

Route (Oral …)

m_routeofadmin

Shared by all

NO, EXISTS

Frequency, duration, dose

m_drugfrequency, m_drugduration, m_drugdose

Shared by all

NO, EXISTS

Log in with the state's own provider admin login and pick State = the state, Service = MMU on every screen. A master saved under another line (another state, or the Stop TB line) will not show for this state's vans.

Find the provider line of a state:

Central · provider lines of a state
SELECT p.ProviderServiceMapID AS line, sp.ServiceProviderName AS provider, s.ServiceName, st.StateName
FROM db_iemr.m_providerservicemapping p
JOIN db_iemr.m_state st                ON st.StateID = p.StateID
LEFT JOIN db_iemr.m_servicemaster s    ON s.ServiceID = p.ServiceID
LEFT JOIN db_iemr.m_serviceprovider sp ON sp.ServiceProviderID = p.ServiceProviderID
WHERE st.StateName LIKE 'Sample State%' AND p.Deleted = 0;

Use the MMU line for all inventory masters and for the camp operator. The Stop TB line is only for the tab nurse and registrar.

5. Admin portal steps, screen by screen

All screens are under Admin portal → Inventory → Master. Do them in this order: each one uses what the one before it created.

Every screen works the same way.

  1. At the top, choose State = your state and Serviceline = MMU. The table shows what already exists.
  2. Click the screen's create button (for example Add UOM).
  3. Fill the fields and click add (the + icon). The row goes into a list below the form. You can add several rows.
  4. Click Save. Nothing is stored until you click Save.

To change a saved row, use the edit icon in the table, then Update.

5.1 · Unit of Measure Master

Why: every medicine's strength needs a unit. Item Master cannot be saved without one.

  1. Choose State and Serviceline (MMU), then click Add UOM.
  2. UOM Code (required, must be unique), UOM Name (required), UOM Description (optional).
  3. Click add for each unit, then Save.

UOM Code

UOM Name

Used for

MG

mg

Tablets, capsules (500 mg)

ML

ml

Syrups (5 ml)

MCG

mcg

Small-dose tablets

PCT

%

Creams, ointments (1 %)

GM

gm

Powders, ORS

Check · UOM of the van's line
SET @van = 101;
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = @van);
SELECT UOMID, UOMName, UOMCode, CreatedBy, CreatedDate
FROM db_iemr.m_uom WHERE ProviderServiceMapID = @line AND Deleted = 0 ORDER BY UOMName;
-- expected: one row per unit you saved

5.2 · Item Category Master

Why: every medicine belongs to a category, and the category decides which batch is given first.

  1. Choose State and Serviceline (MMU), then click Add Category.
  2. Category Code (e.g. DRUG), Category Name (e.g. Drug), Category Description.
  3. Click add, then Save.

5.3 · Item Issue Method Config

Why: when the nurse submits an OPD, the laptop gives medicine from batches in this order.

  1. Choose State and Serviceline (MMU), then click Create Item Config.
  2. Item Category Name = Drug, Issue Type = First Expiry First Out.
  3. Click add, then Save.

Issue Type

Batch given first

First Expiry First Out (recommended)

The batch that expires soonest

First In First Out

The batch entered first

Not set

The laptop uses First Expiry First Out

In every case the laptop skips a batch that expires before the prescribed course ends.

Check · categories and issue type
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = 101);
SELECT ItemCategoryID, ItemCategoryName, IssueType
FROM db_iemr.m_itemcategory WHERE ProviderServiceMapID = @line AND Deleted = 0;
-- expected: Drug | First Expiry First Out

5.4 · Item Master

Why: these are the medicines. They are created once per state, and every van of the state uses the same list.

  1. Choose State and Serviceline (MMU), then click Add Item.
  2. Fill the fields below, click add, and repeat for each medicine. Then Save.

Field

What to enter

Item Code, Item Name

Unique code and the name the nurse will see, e.g. PCM500 / Paracetamol

Medical / Non-Medical

Medical

Item Form

Required for the app. Tablet, Capsule, Syrup … The nurse picks the form first, then sees only medicines of that form.

Strength + UOM

e.g. 500 + mg (UOM from 5.1)

Item Category

Drug (from 5.2)

Route Of Admin

Oral, Local application …

Composition

Generic name, e.g. Paracetamol

Scheduled / Non-Scheduled

As per the drug

Pharmacology Category, Manufacturer, Snomed CT, Drug List Type, Item Description

Optional

Min Stock (low stock alert)

Only on the newer Admin build. Optional.

Check · items of the van's line
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = 101);
SELECT i.ItemID, i.ItemCode, i.ItemName, f.ItemFormName AS Form, i.Strength, u.UOMName AS UOM,
       c.ItemCategoryName AS Category, r.RouteName AS Route
FROM db_iemr.m_item i
LEFT JOIN db_iemr.m_itemform f     ON f.ItemFormID     = i.ItemFormID
LEFT JOIN db_iemr.m_uom u          ON u.UOMID          = i.UOMID
LEFT JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID = i.ItemCategoryID
LEFT JOIN db_iemr.m_routeofadmin r ON r.RouteID        = i.RouteID
WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0
ORDER BY i.ItemName;
-- every row needs Form, UOM and Category

5.5 · Facility Type Master

Why: every store needs a type.

  1. Choose State and Serviceline (MMU), then click Add Facility Type.
  2. Facility Type Code, Facility Type Name, Facility Description. Create two: Main (for hub stores) and Camp (for van stores). One type for both also works.
  3. Click add, then Save.

5.6 · Main Store & Sub Store

Why: stock is held in stores. Each hub has one main store; each van has its own sub store under it.

  1. Choose State and Serviceline (MMU), then click Create Store.
  2. For the hub: Facility Type, Facility Code, Facility Name (e.g. Sample Hub Main), Location, Physical Location, select Main Store. Click add.
  3. For each van: same fields, Facility Name after the van (e.g. Sample Van1 Camp), select Sub Store, then pick its Main Store (the hub's main store). Click add.
  4. Click Save.

    A van must get a Sub Store, never a Main Store. Inventory on the laptop lists a van's store only as a sub store under its main store, and finds the van from that sub store.

    Check · stores of the line
    SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = 101);
SELECT f.FacilityID, f.FacilityName, f.IsMainFacility + 0 AS is_main, f.MainFacilityID AS parent,
       (SELECT GROUP_CONCAT(VanID) FROM db_iemr.m_van WHERE FacilityID = f.FacilityID AND Deleted = 0)                   AS linked_vans,
       (SELECT GROUP_CONCAT(ParkingPlaceID) FROM db_iemr.m_parkingplace WHERE FacilityID = f.FacilityID AND Deleted = 0) AS linked_hubs,
       (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m WHERE m.FacilityID = f.FacilityID AND m.Deleted = 0)      AS items
FROM db_iemr.m_facility f
WHERE f.ProviderServiceMapID = @line AND f.Deleted = 0
ORDER BY f.FacilityID;

Expected after 5.7 and 5.8: each main store has is_main = 1 and its hub in linked_hubs; each sub store has is_main = 0, the main store as parent, its van in linked_vans, and items above 0.

5.7 · Van/Parking Place to Store Mapping

Why: this tells the system which store belongs to which hub and which van. Without it the van has no medicines.

  1. Choose State and Serviceline (MMU), then click Map Store.
  2. Hub: select Main Store, pick the main store and the Parking Place. Click add.
  3. Each van: select Sub Store, pick the main store, then the sub store, then the Van. Click add. The van list shows only vans of that main store's hub that have no store yet.
  4. Click Save.

On some service lines the screen says Hub and Spoke instead of Parking Place and Van.

5.8 · Item To Store Mapping

Why: the medicines mapped to the van's sub store are exactly the list the nurse sees in the app.

  1. Choose State and Serviceline (MMU), then click Add Mapping.
  2. Select Sub Store, pick the Main Store, then the van's Sub Store.
  3. Pick the Item Category (Drug), then either Individual Mapping (tick each medicine under Item Name) or Bulk Mapping (all medicines of that category).
  4. Click add, then Save. Repeat for each van's sub store.

Mapping the medicines to the main store as well is optional; it is used for refills.

Not needed for StopTB dispensing: Supplier Master, Manufacturer Master, Pharmacological Category Master, Expiry Date Alert Config, Item To Facility Mapping.

One check for all masters of the van's line:

Central · masters summary
SET @van = 101;
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = @van);
SELECT 1 AS step, 'UOM' AS master, COUNT(*) AS total_rows, GROUP_CONCAT(UOMName ORDER BY UOMName SEPARATOR ', ') AS detail
  FROM db_iemr.m_uom WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 2, 'Item category', COUNT(*), GROUP_CONCAT(CONCAT(ItemCategoryName,' (',IFNULL(IssueType,'no issue type'),')') SEPARATOR ', ')
  FROM db_iemr.m_itemcategory WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 3, 'Facility type', COUNT(*), GROUP_CONCAT(FacilityTypeName SEPARATOR ', ')
  FROM db_iemr.m_facilitytype WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 4, 'Items', COUNT(*), CONCAT(SUM(ItemFormID IS NULL),' without form, ',SUM(UOMID IS NULL),' without UOM, ',SUM(ItemCategoryID IS NULL),' without category')
  FROM db_iemr.m_item WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 5, 'Items using UOM of another line', COUNT(*), GROUP_CONCAT(i.ItemName SEPARATOR ', ')
  FROM db_iemr.m_item i JOIN db_iemr.m_uom u ON u.UOMID = i.UOMID
  WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0 AND u.ProviderServiceMapID <> @line
UNION ALL SELECT 6, 'Items using category of another line', COUNT(*), GROUP_CONCAT(i.ItemName SEPARATOR ', ')
  FROM db_iemr.m_item i JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID = i.ItemCategoryID
  WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0 AND c.ProviderServiceMapID <> @line
UNION ALL SELECT 7, 'Stores', COUNT(*), GROUP_CONCAT(CONCAT(FacilityID,' ',FacilityName,IF(IsMainFacility+0=1,' [main]',CONCAT(' [sub of ',IFNULL(MainFacilityID,'?'),']'))) SEPARATOR ', ')
  FROM db_iemr.m_facility WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 8, 'Item-store mappings', COUNT(*), CONCAT(COUNT(DISTINCT FacilityID),' stores, ',COUNT(DISTINCT ItemID),' items')
  FROM db_iemr.m_itemfacilitymapping WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 9, 'Items not mapped to any store', COUNT(*), GROUP_CONCAT(i.ItemName SEPARATOR ', ')
  FROM db_iemr.m_item i WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0
  AND NOT EXISTS (SELECT 1 FROM db_iemr.m_itemfacilitymapping m WHERE m.ItemID = i.ItemID AND m.Deleted = 0);

Step

Good result

1–3

More than 0 rows; category shows First Expiry First Out (or no issue type, which the laptop treats as First Expiry First Out)

4

More than 0 items, and 0 without form / UOM / category

5, 6

0 (nothing borrowed from another state)

7

Main stores shown as "main", and one store shown as "sub of <main store ID>" per van

8, 9

Mappings cover every van's sub store; 9 is 0 or only items left out on purpose

6. Users for the van

User

Line

Roles

Mapping

Uses

Camp operator (one per van)

MMU

Pharmacist and Data Sync (others optional)

Hub → this one van

Laptop: MMU UI and Inventory UI with the same login

Nurse

Stop TB

Nurse

Team VanID = the van

Tab app: OPD

Registrar

Stop TB

Registrar

Team VanID = the van

Tab app: registration

No separate inventory user is created. The Inventory UI login checks two things:

  1. The user has a Pharmacist role (role order does not matter).
  2. The user's Designation is the name of one of the user's mapped role screens. A designation of Nurse is fine when the user also has the Nurse role.
Central · operator roles and designation
SELECT u.UserName, d.DesignationName, r.RoleName, m.ProviderServiceMapID AS line, m.USRMappingID
FROM db_iemr.m_user u
LEFT JOIN db_iemr.m_designation d       ON d.DesignationID = u.DesignationID
JOIN db_iemr.m_userservicerolemapping m ON m.UserID = u.UserID AND m.Deleted = 0
JOIN db_iemr.m_role r                   ON r.RoleID = m.RoleID
WHERE u.UserName = 'sample_op1'
ORDER BY m.USRMappingID;
Central · role screens mapped for the line
SELECT r.RoleName, s.ScreenName
FROM db_iemr.m_servicerolescreenmapping srs
JOIN db_iemr.m_role r   ON r.RoleID   = srs.RoleID
JOIN db_iemr.m_screen s ON s.ScreenID = srs.ScreenID
WHERE srs.ProviderServiceMapID = 1001 AND srs.Deleted = 0
  AND r.RoleName IN ('Pharmacist', 'Nurse', 'Data Sync')
ORDER BY r.RoleName;

The tab users' team VanID is stored in m_userservicerolemapping.VanID, added by AMRIT-DB V128. If a query on this column fails with Unknown column 'VanID', central has not run V128 yet: raise it with DevOps.

7. Full check for one van

Run on central after the admin finishes. Each row reports OK or MISSING. Change only the van ID.

Central · complete van inventory check
SET @van = 101;
SELECT x.step, x.check_name, x.value, IF(x.ok, 'OK', 'MISSING / WRONG') AS status
FROM (
  SELECT 1 step, 'Van, line' check_name, (SELECT CONCAT(VanID,' ',VanName,' / line ',ProviderServiceMapID) FROM db_iemr.m_van WHERE VanID=@van AND Deleted=0) value, EXISTS(SELECT 1 FROM db_iemr.m_van WHERE VanID=@van AND Deleted=0) ok
  UNION ALL SELECT 2, 'Hub', (SELECT CONCAT(p.ParkingPlaceID,' ',p.ParkingPlaceName) FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van)
  UNION ALL SELECT 3, 'Service point', (SELECT GROUP_CONCAT(DISTINCT ServicePointID) FROM db_iemr.m_vanservicepointmap WHERE VanID=@van AND Deleted=0), EXISTS(SELECT 1 FROM db_iemr.m_vanservicepointmap WHERE VanID=@van AND Deleted=0)
  UNION ALL SELECT 4, 'Hub main store', (SELECT CONCAT(f.FacilityID,' ',f.FacilityName) FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID JOIN db_iemr.m_facility f ON f.FacilityID=p.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID JOIN db_iemr.m_facility f ON f.FacilityID=p.FacilityID AND f.IsMainFacility=1 WHERE v.VanID=@van)
  UNION ALL SELECT 5, 'Van sub store (Map Store)', (SELECT CONCAT(f.FacilityID,' ',f.FacilityName) FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID AND f.Deleted=0 WHERE v.VanID=@van)
  UNION ALL SELECT 6, 'Sub store: Is Main = No', (SELECT f.IsMainFacility+0 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID AND f.IsMainFacility=0 WHERE v.VanID=@van)
  UNION ALL SELECT 7, 'Sub store parent = hub main store', (SELECT CONCAT('parent ',IFNULL(f.MainFacilityID,'none'),' / hub store ',IFNULL(p.FacilityID,'none')) FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van AND f.MainFacilityID=p.FacilityID)
  UNION ALL SELECT 8, 'Sub store line = van line', (SELECT CONCAT(f.ProviderServiceMapID,' / ',v.ProviderServiceMapID) FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van AND f.ProviderServiceMapID=v.ProviderServiceMapID)
  UNION ALL SELECT 9, 'Items mapped to sub store', (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID WHERE v.VanID=@van AND m.Deleted=0), (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID WHERE v.VanID=@van AND m.Deleted=0) > 0
  UNION ALL SELECT 10, 'Drug list rows (what the app gets)', (SELECT COUNT(*) FROM db_iemr.v_drugforprescription d JOIN db_iemr.m_van v ON v.FacilityID=d.facilityID WHERE v.VanID=@van), (SELECT COUNT(*) FROM db_iemr.v_drugforprescription d JOIN db_iemr.m_van v ON v.FacilityID=d.facilityID WHERE v.VanID=@van) > 0
  UNION ALL SELECT 11, 'Drug category issue type (FEFO)', (SELECT GROUP_CONCAT(DISTINCT CONCAT(c.ItemCategoryName,': ',IFNULL(c.IssueType,'not set = FEFO'))) FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID JOIN db_iemr.m_item i ON i.ItemID=m.ItemID JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID=i.ItemCategoryID WHERE v.VanID=@van AND m.Deleted=0), NOT EXISTS(SELECT 1 FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID JOIN db_iemr.m_item i ON i.ItemID=m.ItemID JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID=i.ItemCategoryID WHERE v.VanID=@van AND m.Deleted=0 AND IFNULL(c.IssueType,'First Expiry First Out') NOT LIKE 'First Expiry%') AND EXISTS(SELECT 1 FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID WHERE v.VanID=@van AND m.Deleted=0)
  UNION ALL SELECT 12, 'Operator mapped to van', (SELECT GROUP_CONCAT(DISTINCT u.UserName) FROM db_iemr.m_uservanmapping uv JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID=uv.UserParkingPlaceMapID AND up.Deleted=0 JOIN db_iemr.m_user u ON u.UserID=up.UserID WHERE uv.VanID=@van AND uv.Deleted=0), EXISTS(SELECT 1 FROM db_iemr.m_uservanmapping uv WHERE uv.VanID=@van AND uv.Deleted=0)
  UNION ALL SELECT 13, 'Operator has Pharmacist + Data Sync', (SELECT GROUP_CONCAT(DISTINCT CONCAT(u.UserName,': ',r.RoleName)) FROM db_iemr.m_uservanmapping uv JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID=uv.UserParkingPlaceMapID AND up.Deleted=0 JOIN db_iemr.m_user u ON u.UserID=up.UserID JOIN db_iemr.m_userservicerolemapping m ON m.UserID=u.UserID AND m.Deleted=0 JOIN db_iemr.m_role r ON r.RoleID=m.RoleID AND r.RoleName IN ('Pharmacist','Data Sync') WHERE uv.VanID=@van AND uv.Deleted=0), (SELECT COUNT(DISTINCT r.RoleName) FROM db_iemr.m_uservanmapping uv JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID=uv.UserParkingPlaceMapID AND up.Deleted=0 JOIN db_iemr.m_userservicerolemapping m ON m.UserID=up.UserID AND m.Deleted=0 JOIN db_iemr.m_role r ON r.RoleID=m.RoleID AND r.RoleName IN ('Pharmacist','Data Sync') WHERE uv.VanID=@van AND uv.Deleted=0) = 2
  UNION ALL SELECT 14, 'Stock in sub store (after laptop upload)', (SELECT CONCAT(COUNT(*),' batches / ',IFNULL(SUM(e.QuantityInHand),0),' in hand') FROM db_iemr.t_itemstockentry e JOIN db_iemr.m_van v ON v.FacilityID=e.FacilityID WHERE v.VanID=@van AND e.Deleted=0), TRUE
) x ORDER BY x.step;

Step

If MISSING / WRONG

1–3

Van configuration incomplete. Finish it first.

4

Main store not mapped to the hub (5.6, 5.7). Needed for refills.

5

Van not mapped to any store (5.7). The app shows no medicines.

6

The van's store was created as a Main Store. Create a Sub Store and map the van to it.

7, 8

Sub store under the wrong main store or wrong line.

9, 10

No items mapped to the van's sub store (5.8), or items lack a form.

11

Category set to First In First Out (5.3). Not set is OK: the laptop uses First Expiry First Out. MISSING also when step 9 is 0.

12, 13

Operator not mapped to the van, or missing Pharmacist / Data Sync.

14

Always OK. Stock is entered on the laptop and reaches central only after Upload.

8. Laptop: Download Masters

Everything above is done on central. The camp laptop receives it with Data Sync → Download Masters. Running it again later is safe and brings any later admin change.

Before downloading

Check

Must be

/opt/amrit/config/.env

STOPTB_ENFORCE_VANID=true, STOPTB_VAN_ID=<this laptop's van>, CENTRAL_URL = the same central the masters were created on

MySQL mode

SELECT @@GLOBAL.sql_mode; must not contain ONLY_FULL_GROUP_BY (otherwise the drug list API fails and the tab keeps an old list)

Services

Restarted after any .env change: sudo systemctl restart 'amrit-*'

Village download date

Not NULL (see query below). A NULL date makes the laptop request every village and overloads central.

Laptop · village download date
SELECT TableName, LastDownloadDate
FROM db_iemr.m_downloadmastertable
WHERE TableName = 'm_DistrictBranchMapping';
-- a date: fine. NULL: stop and escalate (AMRIT-DB V128 fills it on start-up).

Steps

  1. Open MMU on the laptop, log in as the camp operator.
  2. Data Sync → Download Masters. All masters should succeed. If a few fail on the first run, run it once more (hubs must arrive before vans). If every master fails quickly, log out, log in again and retry.
  3. Run the laptop check below.
  4. Open Inventory with the same login: Main Store = No → hub main store → the van's sub store, then Physical Stock Entry (batch, future expiry, quantity).
  5. On the tab, the nurse logs out and back in, opens a new General OPD, picks the Drug Form, and the medicines appear.
Laptop · after Download Masters
SET @van = 101;
SELECT v.VanID, v.FacilityID AS sub_store_id, f.FacilityName AS sub_store,
       f.IsMainFacility + 0 AS is_main, f.MainFacilityID AS parent,
       (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m WHERE m.FacilityID = v.FacilityID AND m.Deleted = 0) AS items_mapped,
       (SELECT COUNT(*) FROM db_iemr.v_drugforprescription d WHERE d.facilityID = v.FacilityID) AS drug_list
FROM db_iemr.m_van v
LEFT JOIN db_iemr.m_facility f ON f.FacilityID = v.FacilityID
WHERE v.VanID = @van;
-- must match central: same sub store, is_main 0, same parent, items_mapped = drug_list

What Download Masters changes

Tables

How

Stores, van, hub, items, UOM, category, facility type, item mapping, role mapping (service-line masters)

Downloaded in full every time on a StopTB laptop, inserted or updated by the same IDs as central

Users (designation etc.), shared masters such as villages

Only rows changed since the last download

Patients, visits, prescriptions, stock, sync flags

Never touched

Nothing is deleted on the laptop; rows deleted on central arrive with Deleted = 1. Central's version always wins for masters, so masters are changed on central only.

9. How inventory follows the van ID

Each laptop is fixed to one van by STOPTB_VAN_ID in .env. The laptop database holds every state's masters (they all come from central); the van ID decides which ones are used.

Part

Van taken from

Store used

Tab drug list (TM-API)

.env STOPTB_VAN_ID (the van the app sends is ignored)

m_van.FacilityID of that van → items mapped to it

Dispensing on OPD submit (FLW-API)

.env STOPTB_VAN_ID

Same sub store; batches given in the category issue order (default First Expiry First Out), skipping batches that expire before the course ends; refused if stock is short

Inventory UI (stock entry, adjustment)

The operator's choice at login

The sub store the operator picks; Inventory finds the van from it

Data Sync upload

Each row carries VanID + its own row ID

Central keeps every van's rows apart (VanSerialNo + VanID)

The operator must pick the sub store of the .env van in Inventory. If they pick another store, stock goes into store A while the nurse dispenses from store B.

Item IDs are the same on every laptop because items are created only on central. Stock is not shared: each batch row belongs to one store, so the same medicine on two laptops is two separate counts.

10. Problems and fixes

Symptom

Cause

Fix

Medicine dropdown empty in the app

Van has no store, or no items mapped to it (check steps 5, 9, 10)

Admin portal: sub store, map van, map items. Laptop: Download Masters. Tab: log out / in, new OPD.

Medicine field disabled or empty for one form

Drug Form not chosen, or no mapped item of that form

Pick the right form (Tablet, Capsule, Syrup …)

Tab shows another state's medicines

Old list kept on the tab after a failed refresh, or wrong STOPTB_VAN_ID

Check .env and sql_mode; nurse logs out / in and opens a new OPD

A medicine disappeared from the list

All its batches have expired

Enter a new batch with a later expiry (Physical Stock Entry)

Every OPD with medicines refused

No usable stock in the van's sub store, or a batch expires before the course ends

Physical Stock Entry with a far expiry date

Both hub stores created as Main

Wrong option in Main Store & Sub Store

Create a Sub Store under the hub's main store; leave the extra main store unused

Items mapped but app still empty

Items mapped to a store no van uses

Map them to the van's sub store (check: linked_vans of the store)

Inventory login: "Designation is not matched with your roles"

No Pharmacist role, or designation not among the user's role screens

Add the Pharmacist role, or set the designation to a role the user has

Inventory shows no store to pick

Store not a sub store of a main store, or on another line

Fix in Main Store & Sub Store (parent, line)

Download Masters: all fail within seconds

Central session expired

Log out, log in, retry

Download Masters: a few fail on first run

Hubs arrive after vans

Run Download Masters again

Query error "Unknown column 'VanID'"

Central is below AMRIT-DB V128

DevOps: deploy AMRIT-DB from main

Stock Adjustment shows 502 on the laptop

It calls the eAushadhi service, which is not on camp laptops

Expected; use Physical Stock Entry

11. Rules

  1. One laptop = one van = one sub store, with a unique STOPTB_VAN_ID per laptop.
  2. Create and change masters (items, stores, mappings, users) only on central, with the state's provider admin login. Never on a laptop.
  3. Laptops are set up from a backup of the same central they download from. A backup from another environment gives different IDs.
  4. Camp stock is entered only on the laptop. Never enter camp sub-store stock on central.
  5. Don't change expiry dates in the database. Add a new batch through Physical Stock Entry.
  6. Check queries are SELECT only. Every change is made through the Admin portal screens, never by SQL.

Source: AMRIT release-3.12.0 (MMU-API, TM-API, FLW-API, Inventory-UI, ADMIN-UI, AMRIT-DB). Queries tested on the StopTB test database.

How to set up medicines and stores for a StopTB van that is already configured, bring them to the camp laptop, and confirm every link with read-only queries.

1. What this covers

The van configuration (zone, hub, van, service point, operator user) is already done by the L2 team. This page covers what comes after it: the medicine masters, the stores, the store mappings, the laptop download, and the checks that prove the van will show medicines in the StopTB app.

Who

Does

Where

L2 support (logged in with the state's provider admin login, e.g. AdminSample)

UOM, category, items, stores, store mapping, item mapping; then runs the check queries (SELECT only)

Admin portal → Inventory → Master on central; queries on the central DB and the laptop DB

Camp operator (e.g. sample_op1)

Download Masters, Physical Stock Entry, Data Sync Upload

Camp laptop (MMU UI + Inventory UI)

Tab nurse / registrar

Registration and OPD; medicines are given automatically

StopTB tab app, connected to the laptop

Sample values: all IDs and names on this page are examples, not real data: provider line 1001 (Sample State, MMU), hub 11, vans 101 and 102, operator sample_op1, sub store Sample Van1 Camp. Replace them with your own before running a query.

Every query on this page is read-only. SET @van = … only creates a variable in your own Workbench session. Nothing is inserted, updated or deleted.

2. How a van gets its medicines

There is no table that links a van directly to medicines. The link always goes through the van's own store. One laptop serves one van, and that van has one camp sub-store.

Provider line (state + MMU, e.g. 1001 Sample State MMU)
 ├── UOM, Item Category, Facility Type, Item Master       (created once per state)
 │
 └── Hub (parking place, e.g. 11)
       └── Main store            IsMainFacility = 1       m_parkingplace.FacilityID → main store
             ├── Sub store A     IsMainFacility = 0, parent = main store
             │     ├── van 101   m_van.FacilityID → sub store A
             │     └── items     m_itemfacilitymapping (sub store A + item)
             └── Sub store B     IsMainFacility = 0, parent = main store
                   ├── van 102   m_van.FacilityID → sub store B
                   └── items     m_itemfacilitymapping (sub store B + item)

App drug list = items mapped to the van's sub store (view v_drugforprescription)

A medicine appears in the app's dropdown only when all of these are true:

  1. The van is mapped to a store (m_van.FacilityID is filled).
  2. The medicine is mapped to that store (m_itemfacilitymapping, not deleted).
  3. The item and the mapping are on the same provider line.
  4. The item has a Drug Form and is not deleted.
  5. The item has no batches yet (shows with quantity 0), or at least one batch that has not expired. If every batch has expired, the medicine is hidden.
  6. In the app, the nurse picks the matching Drug Form first (Tablet, Capsule, Syrup …). The medicine list is filtered by that form.

3. Before you start: van check

Confirm the van configuration is complete before touching inventory. Replace 101 with your van ID. Run on central.

Central · van configuration
SET @van = 101;
SELECT v.VanID, v.VanName, v.ProviderServiceMapID AS line,
       p.ParkingPlaceID AS hub_id, p.ParkingPlaceName AS hub,
       (SELECT GROUP_CONCAT(DISTINCT ServicePointID) FROM db_iemr.m_vanservicepointmap
         WHERE VanID = v.VanID AND Deleted = 0) AS service_point,
       (SELECT GROUP_CONCAT(DISTINCT u.UserName) FROM db_iemr.m_uservanmapping uv
          JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID = uv.UserParkingPlaceMapID AND up.Deleted = 0
          JOIN db_iemr.m_user u ON u.UserID = up.UserID
         WHERE uv.VanID = v.VanID AND uv.Deleted = 0) AS operator
FROM db_iemr.m_van v
LEFT JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID = v.ParkingPlaceID
WHERE v.VanID = @van AND v.Deleted = 0;

Expected: one row with the line, hub, service point and the operator's username all filled. If any is empty, finish the van configuration first.

To find a van ID from its name:

Central · find vans of a state
SELECT v.VanID, v.VanName, v.ProviderServiceMapID AS line, pp.ParkingPlaceName AS hub,
       v.FacilityID AS store_id, f.FacilityName AS store
FROM db_iemr.m_van v
JOIN db_iemr.m_providerservicemapping p ON p.ProviderServiceMapID = v.ProviderServiceMapID
JOIN db_iemr.m_state st                 ON st.StateID = p.StateID
LEFT JOIN db_iemr.m_parkingplace pp     ON pp.ParkingPlaceID = v.ParkingPlaceID
LEFT JOIN db_iemr.m_facility f          ON f.FacilityID = v.FacilityID
WHERE v.Deleted = 0 AND st.StateName LIKE 'Sample State%'
ORDER BY v.VanID;

4. Which masters belong to the state

Each provider line keeps its own inventory masters. Another state's UOMs or items are not visible to this state, so a new state starts empty. A few masters are shared by everyone and must not be created again.

Master

Table

Scope

Admin creates it?

Unit of measure

m_uom

Per provider line

YES

Item category (+ issue method)

m_itemcategory

Per provider line

YES

Facility type

m_facilitytype

Per provider line

YES

Item (medicine)

m_item

Per provider line

YES

Stores

m_facility

Per provider line

YES

Item to store mapping

m_itemfacilitymapping

Per store

YES

Drug form (Tablet, Syrup …)

m_itemform

Shared by all

NO, EXISTS

Route (Oral …)

m_routeofadmin

Shared by all

NO, EXISTS

Frequency, duration, dose

m_drugfrequency, m_drugduration, m_drugdose

Shared by all

NO, EXISTS

Log in with the state's own provider admin login and pick State = the state, Service = MMU on every screen. A master saved under another line (another state, or the Stop TB line) will not show for this state's vans.

Find the provider line of a state:

Central · provider lines of a state
SELECT p.ProviderServiceMapID AS line, sp.ServiceProviderName AS provider, s.ServiceName, st.StateName
FROM db_iemr.m_providerservicemapping p
JOIN db_iemr.m_state st                ON st.StateID = p.StateID
LEFT JOIN db_iemr.m_servicemaster s    ON s.ServiceID = p.ServiceID
LEFT JOIN db_iemr.m_serviceprovider sp ON sp.ServiceProviderID = p.ServiceProviderID
WHERE st.StateName LIKE 'Sample State%' AND p.Deleted = 0;

Use the MMU line for all inventory masters and for the camp operator. The Stop TB line is only for the tab nurse and registrar.

5. Admin portal steps, screen by screen

All screens are under Admin portal → Inventory → Master. Do them in this order: each one uses what the one before it created.

Every screen works the same way.

  1. At the top, choose State = your state and Serviceline = MMU. The table shows what already exists.
  2. Click the screen's create button (for example Add UOM).
  3. Fill the fields and click add (the + icon). The row goes into a list below the form. You can add several rows.
  4. Click Save. Nothing is stored until you click Save.

To change a saved row, use the edit icon in the table, then Update.

5.1 · Unit of Measure Master

Why: every medicine's strength needs a unit. Item Master cannot be saved without one.

  1. Choose State and Serviceline (MMU), then click Add UOM.
  2. UOM Code (required, must be unique), UOM Name (required), UOM Description (optional).
  3. Click add for each unit, then Save.

UOM Code

UOM Name

Used for

MG

mg

Tablets, capsules (500 mg)

ML

ml

Syrups (5 ml)

MCG

mcg

Small-dose tablets

PCT

%

Creams, ointments (1 %)

GM

gm

Powders, ORS

Check · UOM of the van's line
SET @van = 101;
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = @van);
SELECT UOMID, UOMName, UOMCode, CreatedBy, CreatedDate
FROM db_iemr.m_uom WHERE ProviderServiceMapID = @line AND Deleted = 0 ORDER BY UOMName;
-- expected: one row per unit you saved

5.2 · Item Category Master

Why: every medicine belongs to a category, and the category decides which batch is given first.

  1. Choose State and Serviceline (MMU), then click Add Category.
  2. Category Code (e.g. DRUG), Category Name (e.g. Drug), Category Description.
  3. Click add, then Save.

5.3 · Item Issue Method Config

Why: when the nurse submits an OPD, the laptop gives medicine from batches in this order.

  1. Choose State and Serviceline (MMU), then click Create Item Config.
  2. Item Category Name = Drug, Issue Type = First Expiry First Out.
  3. Click add, then Save.

Issue Type

Batch given first

First Expiry First Out (recommended)

The batch that expires soonest

First In First Out

The batch entered first

Not set

The laptop uses First Expiry First Out

In every case the laptop skips a batch that expires before the prescribed course ends.

Check · categories and issue type
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = 101);
SELECT ItemCategoryID, ItemCategoryName, IssueType
FROM db_iemr.m_itemcategory WHERE ProviderServiceMapID = @line AND Deleted = 0;
-- expected: Drug | First Expiry First Out

5.4 · Item Master

Why: these are the medicines. They are created once per state, and every van of the state uses the same list.

  1. Choose State and Serviceline (MMU), then click Add Item.
  2. Fill the fields below, click add, and repeat for each medicine. Then Save.

Field

What to enter

Item Code, Item Name

Unique code and the name the nurse will see, e.g. PCM500 / Paracetamol

Medical / Non-Medical

Medical

Item Form

Required for the app. Tablet, Capsule, Syrup … The nurse picks the form first, then sees only medicines of that form.

Strength + UOM

e.g. 500 + mg (UOM from 5.1)

Item Category

Drug (from 5.2)

Route Of Admin

Oral, Local application …

Composition

Generic name, e.g. Paracetamol

Scheduled / Non-Scheduled

As per the drug

Pharmacology Category, Manufacturer, Snomed CT, Drug List Type, Item Description

Optional

Min Stock (low stock alert)

Only on the newer Admin build. Optional.

Check · items of the van's line
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = 101);
SELECT i.ItemID, i.ItemCode, i.ItemName, f.ItemFormName AS Form, i.Strength, u.UOMName AS UOM,
       c.ItemCategoryName AS Category, r.RouteName AS Route
FROM db_iemr.m_item i
LEFT JOIN db_iemr.m_itemform f     ON f.ItemFormID     = i.ItemFormID
LEFT JOIN db_iemr.m_uom u          ON u.UOMID          = i.UOMID
LEFT JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID = i.ItemCategoryID
LEFT JOIN db_iemr.m_routeofadmin r ON r.RouteID        = i.RouteID
WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0
ORDER BY i.ItemName;
-- every row needs Form, UOM and Category

5.5 · Facility Type Master

Why: every store needs a type.

  1. Choose State and Serviceline (MMU), then click Add Facility Type.
  2. Facility Type Code, Facility Type Name, Facility Description. Create two: Main (for hub stores) and Camp (for van stores). One type for both also works.
  3. Click add, then Save.

5.6 · Main Store & Sub Store

Why: stock is held in stores. Each hub has one main store; each van has its own sub store under it.

  1. Choose State and Serviceline (MMU), then click Create Store.
  2. For the hub: Facility Type, Facility Code, Facility Name (e.g. Sample Hub Main), Location, Physical Location, select Main Store. Click add.
  3. For each van: same fields, Facility Name after the van (e.g. Sample Van1 Camp), select Sub Store, then pick its Main Store (the hub's main store). Click add.
  4. Click Save.

    A van must get a Sub Store, never a Main Store. Inventory on the laptop lists a van's store only as a sub store under its main store, and finds the van from that sub store.

    Check · stores of the line
    SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = 101);
SELECT f.FacilityID, f.FacilityName, f.IsMainFacility + 0 AS is_main, f.MainFacilityID AS parent,
       (SELECT GROUP_CONCAT(VanID) FROM db_iemr.m_van WHERE FacilityID = f.FacilityID AND Deleted = 0)                   AS linked_vans,
       (SELECT GROUP_CONCAT(ParkingPlaceID) FROM db_iemr.m_parkingplace WHERE FacilityID = f.FacilityID AND Deleted = 0) AS linked_hubs,
       (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m WHERE m.FacilityID = f.FacilityID AND m.Deleted = 0)      AS items
FROM db_iemr.m_facility f
WHERE f.ProviderServiceMapID = @line AND f.Deleted = 0
ORDER BY f.FacilityID;

Expected after 5.7 and 5.8: each main store has is_main = 1 and its hub in linked_hubs; each sub store has is_main = 0, the main store as parent, its van in linked_vans, and items above 0.

5.7 · Van/Parking Place to Store Mapping

Why: this tells the system which store belongs to which hub and which van. Without it the van has no medicines.

  1. Choose State and Serviceline (MMU), then click Map Store.
  2. Hub: select Main Store, pick the main store and the Parking Place. Click add.
  3. Each van: select Sub Store, pick the main store, then the sub store, then the Van. Click add. The van list shows only vans of that main store's hub that have no store yet.
  4. Click Save.

On some service lines the screen says Hub and Spoke instead of Parking Place and Van.

5.8 · Item To Store Mapping

Why: the medicines mapped to the van's sub store are exactly the list the nurse sees in the app.

  1. Choose State and Serviceline (MMU), then click Add Mapping.
  2. Select Sub Store, pick the Main Store, then the van's Sub Store.
  3. Pick the Item Category (Drug), then either Individual Mapping (tick each medicine under Item Name) or Bulk Mapping (all medicines of that category).
  4. Click add, then Save. Repeat for each van's sub store.

Mapping the medicines to the main store as well is optional; it is used for refills.

Not needed for StopTB dispensing: Supplier Master, Manufacturer Master, Pharmacological Category Master, Expiry Date Alert Config, Item To Facility Mapping.

One check for all masters of the van's line:

Central · masters summary
SET @van = 101;
SET @line = (SELECT ProviderServiceMapID FROM db_iemr.m_van WHERE VanID = @van);
SELECT 1 AS step, 'UOM' AS master, COUNT(*) AS total_rows, GROUP_CONCAT(UOMName ORDER BY UOMName SEPARATOR ', ') AS detail
  FROM db_iemr.m_uom WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 2, 'Item category', COUNT(*), GROUP_CONCAT(CONCAT(ItemCategoryName,' (',IFNULL(IssueType,'no issue type'),')') SEPARATOR ', ')
  FROM db_iemr.m_itemcategory WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 3, 'Facility type', COUNT(*), GROUP_CONCAT(FacilityTypeName SEPARATOR ', ')
  FROM db_iemr.m_facilitytype WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 4, 'Items', COUNT(*), CONCAT(SUM(ItemFormID IS NULL),' without form, ',SUM(UOMID IS NULL),' without UOM, ',SUM(ItemCategoryID IS NULL),' without category')
  FROM db_iemr.m_item WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 5, 'Items using UOM of another line', COUNT(*), GROUP_CONCAT(i.ItemName SEPARATOR ', ')
  FROM db_iemr.m_item i JOIN db_iemr.m_uom u ON u.UOMID = i.UOMID
  WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0 AND u.ProviderServiceMapID <> @line
UNION ALL SELECT 6, 'Items using category of another line', COUNT(*), GROUP_CONCAT(i.ItemName SEPARATOR ', ')
  FROM db_iemr.m_item i JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID = i.ItemCategoryID
  WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0 AND c.ProviderServiceMapID <> @line
UNION ALL SELECT 7, 'Stores', COUNT(*), GROUP_CONCAT(CONCAT(FacilityID,' ',FacilityName,IF(IsMainFacility+0=1,' [main]',CONCAT(' [sub of ',IFNULL(MainFacilityID,'?'),']'))) SEPARATOR ', ')
  FROM db_iemr.m_facility WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 8, 'Item-store mappings', COUNT(*), CONCAT(COUNT(DISTINCT FacilityID),' stores, ',COUNT(DISTINCT ItemID),' items')
  FROM db_iemr.m_itemfacilitymapping WHERE ProviderServiceMapID = @line AND Deleted = 0
UNION ALL SELECT 9, 'Items not mapped to any store', COUNT(*), GROUP_CONCAT(i.ItemName SEPARATOR ', ')
  FROM db_iemr.m_item i WHERE i.ProviderServiceMapID = @line AND i.Deleted = 0
  AND NOT EXISTS (SELECT 1 FROM db_iemr.m_itemfacilitymapping m WHERE m.ItemID = i.ItemID AND m.Deleted = 0);

Step

Good result

1–3

More than 0 rows; category shows First Expiry First Out (or no issue type, which the laptop treats as First Expiry First Out)

4

More than 0 items, and 0 without form / UOM / category

5, 6

0 (nothing borrowed from another state)

<ac:structured-macro ac:name="unmigrated-wiki-markup" ac:schema-version="1" ac:macro-id="db8582f7-111b-40b5-a62f-b93c0822d358"><ac:plain-text-body><![CDATA[

7

Main stores [main] and one [sub of …] per van

]]></ac:plain-text-body></ac:structured-macro>

8, 9

Mappings cover every van's sub store; 9 is 0 or only items left out on purpose

6. Users for the van

User

Line

Roles

Mapping

Uses

Camp operator (one per van)

MMU

Pharmacist and Data Sync (others optional)

Hub → this one van

Laptop: MMU UI and Inventory UI with the same login

Nurse

Stop TB

Nurse

Team VanID = the van

Tab app: OPD

Registrar

Stop TB

Registrar

Team VanID = the van

Tab app: registration

No separate inventory user is created. The Inventory UI login checks two things:

  1. The user has a Pharmacist role (role order does not matter).
  2. The user's Designation is the name of one of the user's mapped role screens. A designation of Nurse is fine when the user also has the Nurse role.
Central · operator roles and designation
SELECT u.UserName, d.DesignationName, r.RoleName, m.ProviderServiceMapID AS line, m.USRMappingID
FROM db_iemr.m_user u
LEFT JOIN db_iemr.m_designation d       ON d.DesignationID = u.DesignationID
JOIN db_iemr.m_userservicerolemapping m ON m.UserID = u.UserID AND m.Deleted = 0
JOIN db_iemr.m_role r                   ON r.RoleID = m.RoleID
WHERE u.UserName = 'sample_op1'
ORDER BY m.USRMappingID;
Central · role screens mapped for the line
SELECT r.RoleName, s.ScreenName
FROM db_iemr.m_servicerolescreenmapping srs
JOIN db_iemr.m_role r   ON r.RoleID   = srs.RoleID
JOIN db_iemr.m_screen s ON s.ScreenID = srs.ScreenID
WHERE srs.ProviderServiceMapID = 1001 AND srs.Deleted = 0
  AND r.RoleName IN ('Pharmacist', 'Nurse', 'Data Sync')
ORDER BY r.RoleName;

The tab users' team VanID is stored in m_userservicerolemapping.VanID, added by AMRIT-DB V128. If a query on this column fails with Unknown column 'VanID', central has not run V128 yet: raise it with DevOps.

7. Full check for one van

Run on central after the admin finishes. Each row reports OK or MISSING. Change only the van ID.

Central · complete van inventory check
SET @van = 101;
SELECT x.step, x.check_name, x.value, IF(x.ok, 'OK', 'MISSING / WRONG') AS status
FROM (
  SELECT 1 step, 'Van, line' check_name, (SELECT CONCAT(VanID,' ',VanName,' / line ',ProviderServiceMapID) FROM db_iemr.m_van WHERE VanID=@van AND Deleted=0) value, EXISTS(SELECT 1 FROM db_iemr.m_van WHERE VanID=@van AND Deleted=0) ok
  UNION ALL SELECT 2, 'Hub', (SELECT CONCAT(p.ParkingPlaceID,' ',p.ParkingPlaceName) FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van)
  UNION ALL SELECT 3, 'Service point', (SELECT GROUP_CONCAT(DISTINCT ServicePointID) FROM db_iemr.m_vanservicepointmap WHERE VanID=@van AND Deleted=0), EXISTS(SELECT 1 FROM db_iemr.m_vanservicepointmap WHERE VanID=@van AND Deleted=0)
  UNION ALL SELECT 4, 'Hub main store', (SELECT CONCAT(f.FacilityID,' ',f.FacilityName) FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID JOIN db_iemr.m_facility f ON f.FacilityID=p.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID JOIN db_iemr.m_facility f ON f.FacilityID=p.FacilityID AND f.IsMainFacility=1 WHERE v.VanID=@van)
  UNION ALL SELECT 5, 'Van sub store (Map Store)', (SELECT CONCAT(f.FacilityID,' ',f.FacilityName) FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID AND f.Deleted=0 WHERE v.VanID=@van)
  UNION ALL SELECT 6, 'Sub store: Is Main = No', (SELECT f.IsMainFacility+0 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID AND f.IsMainFacility=0 WHERE v.VanID=@van)
  UNION ALL SELECT 7, 'Sub store parent = hub main store', (SELECT CONCAT('parent ',IFNULL(f.MainFacilityID,'none'),' / hub store ',IFNULL(p.FacilityID,'none')) FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID JOIN db_iemr.m_parkingplace p ON p.ParkingPlaceID=v.ParkingPlaceID WHERE v.VanID=@van AND f.MainFacilityID=p.FacilityID)
  UNION ALL SELECT 8, 'Sub store line = van line', (SELECT CONCAT(f.ProviderServiceMapID,' / ',v.ProviderServiceMapID) FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van), EXISTS(SELECT 1 FROM db_iemr.m_van v JOIN db_iemr.m_facility f ON f.FacilityID=v.FacilityID WHERE v.VanID=@van AND f.ProviderServiceMapID=v.ProviderServiceMapID)
  UNION ALL SELECT 9, 'Items mapped to sub store', (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID WHERE v.VanID=@van AND m.Deleted=0), (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID WHERE v.VanID=@van AND m.Deleted=0) > 0
  UNION ALL SELECT 10, 'Drug list rows (what the app gets)', (SELECT COUNT(*) FROM db_iemr.v_drugforprescription d JOIN db_iemr.m_van v ON v.FacilityID=d.facilityID WHERE v.VanID=@van), (SELECT COUNT(*) FROM db_iemr.v_drugforprescription d JOIN db_iemr.m_van v ON v.FacilityID=d.facilityID WHERE v.VanID=@van) > 0
  UNION ALL SELECT 11, 'Drug category issue type (FEFO)', (SELECT GROUP_CONCAT(DISTINCT CONCAT(c.ItemCategoryName,': ',IFNULL(c.IssueType,'not set = FEFO'))) FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID JOIN db_iemr.m_item i ON i.ItemID=m.ItemID JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID=i.ItemCategoryID WHERE v.VanID=@van AND m.Deleted=0), NOT EXISTS(SELECT 1 FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID JOIN db_iemr.m_item i ON i.ItemID=m.ItemID JOIN db_iemr.m_itemcategory c ON c.ItemCategoryID=i.ItemCategoryID WHERE v.VanID=@van AND m.Deleted=0 AND IFNULL(c.IssueType,'First Expiry First Out') NOT LIKE 'First Expiry%') AND EXISTS(SELECT 1 FROM db_iemr.m_itemfacilitymapping m JOIN db_iemr.m_van v ON v.FacilityID=m.FacilityID WHERE v.VanID=@van AND m.Deleted=0)
  UNION ALL SELECT 12, 'Operator mapped to van', (SELECT GROUP_CONCAT(DISTINCT u.UserName) FROM db_iemr.m_uservanmapping uv JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID=uv.UserParkingPlaceMapID AND up.Deleted=0 JOIN db_iemr.m_user u ON u.UserID=up.UserID WHERE uv.VanID=@van AND uv.Deleted=0), EXISTS(SELECT 1 FROM db_iemr.m_uservanmapping uv WHERE uv.VanID=@van AND uv.Deleted=0)
  UNION ALL SELECT 13, 'Operator has Pharmacist + Data Sync', (SELECT GROUP_CONCAT(DISTINCT CONCAT(u.UserName,': ',r.RoleName)) FROM db_iemr.m_uservanmapping uv JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID=uv.UserParkingPlaceMapID AND up.Deleted=0 JOIN db_iemr.m_user u ON u.UserID=up.UserID JOIN db_iemr.m_userservicerolemapping m ON m.UserID=u.UserID AND m.Deleted=0 JOIN db_iemr.m_role r ON r.RoleID=m.RoleID AND r.RoleName IN ('Pharmacist','Data Sync') WHERE uv.VanID=@van AND uv.Deleted=0), (SELECT COUNT(DISTINCT r.RoleName) FROM db_iemr.m_uservanmapping uv JOIN db_iemr.m_userparkingplacemap up ON up.UserParkingPlaceMapID=uv.UserParkingPlaceMapID AND up.Deleted=0 JOIN db_iemr.m_userservicerolemapping m ON m.UserID=up.UserID AND m.Deleted=0 JOIN db_iemr.m_role r ON r.RoleID=m.RoleID AND r.RoleName IN ('Pharmacist','Data Sync') WHERE uv.VanID=@van AND uv.Deleted=0) = 2
  UNION ALL SELECT 14, 'Stock in sub store (after laptop upload)', (SELECT CONCAT(COUNT(*),' batches / ',IFNULL(SUM(e.QuantityInHand),0),' in hand') FROM db_iemr.t_itemstockentry e JOIN db_iemr.m_van v ON v.FacilityID=e.FacilityID WHERE v.VanID=@van AND e.Deleted=0), TRUE
) x ORDER BY x.step;

Step

If MISSING / WRONG

1–3

Van configuration incomplete. Finish it first.

4

Main store not mapped to the hub (5.6, 5.7). Needed for refills.

5

Van not mapped to any store (5.7). The app shows no medicines.

6

The van's store was created as a Main Store. Create a Sub Store and map the van to it.

7, 8

Sub store under the wrong main store or wrong line.

9, 10

No items mapped to the van's sub store (5.8), or items lack a form.

11

Category set to First In First Out (5.3). Not set is OK: the laptop uses First Expiry First Out. MISSING also when step 9 is 0.

12, 13

Operator not mapped to the van, or missing Pharmacist / Data Sync.

14

Always OK. Stock is entered on the laptop and reaches central only after Upload.

8. Laptop: Download Masters

Everything above is done on central. The camp laptop receives it with Data Sync → Download Masters. Running it again later is safe and brings any later admin change.

Before downloading

Check

Must be

/opt/amrit/config/.env

STOPTB_ENFORCE_VANID=true, STOPTB_VAN_ID=<this laptop's van>, CENTRAL_URL = the same central the masters were created on

MySQL mode

SELECT @@GLOBAL.sql_mode; must not contain ONLY_FULL_GROUP_BY (otherwise the drug list API fails and the tab keeps an old list)

Services

Restarted after any .env change: sudo systemctl restart 'amrit-*'

Village download date

Not NULL (see query below). A NULL date makes the laptop request every village and overloads central.

Laptop · village download date
SELECT TableName, LastDownloadDate
FROM db_iemr.m_downloadmastertable
WHERE TableName = 'm_DistrictBranchMapping';
-- a date: fine. NULL: stop and escalate (AMRIT-DB V128 fills it on start-up).

Steps

  1. Open MMU on the laptop, log in as the camp operator.
  2. Data Sync → Download Masters. All masters should succeed. If a few fail on the first run, run it once more (hubs must arrive before vans). If every master fails quickly, log out, log in again and retry.
  3. Run the laptop check below.
  4. Open Inventory with the same login: Main Store = No → hub main store → the van's sub store, then Physical Stock Entry (batch, future expiry, quantity).
  5. On the tab, the nurse logs out and back in, opens a new General OPD, picks the Drug Form, and the medicines appear.
Laptop · after Download Masters
SET @van = 101;
SELECT v.VanID, v.FacilityID AS sub_store_id, f.FacilityName AS sub_store,
       f.IsMainFacility + 0 AS is_main, f.MainFacilityID AS parent,
       (SELECT COUNT(*) FROM db_iemr.m_itemfacilitymapping m WHERE m.FacilityID = v.FacilityID AND m.Deleted = 0) AS items_mapped,
       (SELECT COUNT(*) FROM db_iemr.v_drugforprescription d WHERE d.facilityID = v.FacilityID) AS drug_list
FROM db_iemr.m_van v
LEFT JOIN db_iemr.m_facility f ON f.FacilityID = v.FacilityID
WHERE v.VanID = @van;
-- must match central: same sub store, is_main 0, same parent, items_mapped = drug_list

What Download Masters changes

Tables

How

Stores, van, hub, items, UOM, category, facility type, item mapping, role mapping (service-line masters)

Downloaded in full every time on a StopTB laptop, inserted or updated by the same IDs as central

Users (designation etc.), shared masters such as villages

Only rows changed since the last download

Patients, visits, prescriptions, stock, sync flags

Never touched

Nothing is deleted on the laptop; rows deleted on central arrive with Deleted = 1. Central's version always wins for masters, so masters are changed on central only.

9. How inventory follows the van ID

Each laptop is fixed to one van by STOPTB_VAN_ID in .env. The laptop database holds every state's masters (they all come from central); the van ID decides which ones are used.

Part

Van taken from

Store used

Tab drug list (TM-API)

.env STOPTB_VAN_ID (the van the app sends is ignored)

m_van.FacilityID of that van → items mapped to it

Dispensing on OPD submit (FLW-API)

.env STOPTB_VAN_ID

Same sub store; batches given in the category issue order (default First Expiry First Out), skipping batches that expire before the course ends; refused if stock is short

Inventory UI (stock entry, adjustment)

The operator's choice at login

The sub store the operator picks; Inventory finds the van from it

Data Sync upload

Each row carries VanID + its own row ID

Central keeps every van's rows apart (VanSerialNo + VanID)

The operator must pick the sub store of the .env van in Inventory. If they pick another store, stock goes into store A while the nurse dispenses from store B.

Item IDs are the same on every laptop because items are created only on central. Stock is not shared: each batch row belongs to one store, so the same medicine on two laptops is two separate counts.

10. Problems and fixes

Symptom

Cause

Fix

Medicine dropdown empty in the app

Van has no store, or no items mapped to it (check steps 5, 9, 10)

Admin portal: sub store, map van, map items. Laptop: Download Masters. Tab: log out / in, new OPD.

Medicine field disabled or empty for one form

Drug Form not chosen, or no mapped item of that form

Pick the right form (Tablet, Capsule, Syrup …)

Tab shows another state's medicines

Old list kept on the tab after a failed refresh, or wrong STOPTB_VAN_ID

Check .env and sql_mode; nurse logs out / in and opens a new OPD

A medicine disappeared from the list

All its batches have expired

Enter a new batch with a later expiry (Physical Stock Entry)

Every OPD with medicines refused

No usable stock in the van's sub store, or a batch expires before the course ends

Physical Stock Entry with a far expiry date

Both hub stores created as Main

Wrong option in Main Store & Sub Store

Create a Sub Store under the hub's main store; leave the extra main store unused

Items mapped but app still empty

Items mapped to a store no van uses

Map them to the van's sub store (check: linked_vans of the store)

Inventory login: "Designation is not matched with your roles"

No Pharmacist role, or designation not among the user's role screens

Add the Pharmacist role, or set the designation to a role the user has

Inventory shows no store to pick

Store not a sub store of a main store, or on another line

Fix in Main Store & Sub Store (parent, line)

Download Masters: all fail within seconds

Central session expired

Log out, log in, retry

Download Masters: a few fail on first run

Hubs arrive after vans

Run Download Masters again

Query error "Unknown column 'VanID'"

Central is below AMRIT-DB V128

DevOps: deploy AMRIT-DB from main

Stock Adjustment shows 502 on the laptop

It calls the eAushadhi service, which is not on camp laptops

Expected; use Physical Stock Entry

11. Rules

  1. One laptop = one van = one sub store, with a unique STOPTB_VAN_ID per laptop.
  2. Create and change masters (items, stores, mappings, users) only on central, with the state's provider admin login. Never on a laptop.
  3. Laptops are set up from a backup of the same central they download from. A backup from another environment gives different IDs.
  4. Camp stock is entered only on the laptop. Never enter camp sub-store stock on central.
  5. Don't change expiry dates in the database. Add a new batch through Physical Stock Entry.
  6. Check queries are SELECT only. Every change is made through the Admin portal screens, never by SQL.

Source: AMRIT release-3.12.0 (MMU-API, TM-API, FLW-API, Inventory-UI, ADMIN-UI, AMRIT-DB). Queries tested on the StopTB test database.