Admin Fill Bill Department Type SQL

Fix: Bills Missing Department Type (departmentType = NULL)

Problem

When a department type filter is selected on reports such as the Stock Transfer Report (stock_transfer_report.xhtml), the JPQL query adds:

AND b.departmentType IN :departmentTypes

Bills created before the departmentType feature was introduced have NULL in that column and are therefore excluded, causing the report to return no data even though transfers exist for the selected date range.

Root Cause

The Bill entity stores departmentType as a string enum column (DEPARTMENTTYPE in MySQL). Bills created by older versions of the application (pharmacy transfer requests/issues/receives, GRNs, store issues, etc.) were never backfilled with this value.

Important: PHARMACY_* bill types are not exclusively pharmacy bills. A PHARMACY_ISSUE or PHARMACY_TRANSFER_REQUEST bill can carry store items (e.g. LIQUID NITROGEN) or lab items. The correct department type must be derived from the items on the bill — not assumed from the bill type prefix.

Fast Fix — Native SQL (Two-Step)

This is the recommended approach. Run the two steps in order.

---

Step 1 — Verify scope

See how many NULL bills exist and which bill types are affected:

SELECT BILLTYPEATOMIC, COUNT(*) AS cnt
FROM bill
WHERE DEPARTMENTTYPE IS NULL
  AND RETIRED = 0
GROUP BY BILLTYPEATOMIC
ORDER BY cnt DESC;

---

Step 2 — Backfill from item department type (single-type bills only)

This updates every bill whose items all share the same department type. Bills with mixed item types (e.g. Pharmacy + Store on one bill) are safely skipped by the HAVING COUNT(DISTINCT …) = 1 guard.

UPDATE bill b
JOIN (
    SELECT bi.BILL_ID, MAX(i.DEPARTMENTTYPE) AS derived_dept
    FROM billitem bi
    JOIN item i ON bi.ITEM_ID = i.ID
        AND i.DEPARTMENTTYPE IS NOT NULL
    WHERE bi.RETIRED = 0
    GROUP BY bi.BILL_ID
    HAVING COUNT(DISTINCT i.DEPARTMENTTYPE) = 1
) sub ON b.ID = sub.BILL_ID
SET b.DEPARTMENTTYPE = sub.derived_dept
WHERE b.DEPARTMENTTYPE IS NULL
  AND b.RETIRED = 0;

SELECT ROW_COUNT() AS rows_updated;

---

Step 3 — Handle remaining NULLs (items all null or no items)

Some bills may still have NULL after Step 2 — either they have no bill items, or their items also have NULL department type. For the pharmacy transfer workflow these are safe to default to 'Pharmacy':

-- Preview what remains
SELECT BILLTYPEATOMIC, COUNT(*) AS cnt
FROM bill
WHERE DEPARTMENTTYPE IS NULL AND RETIRED = 0
GROUP BY BILLTYPEATOMIC ORDER BY cnt DESC;

-- Default pharmacy-service bills that couldn't be resolved from items
UPDATE bill
SET DEPARTMENTTYPE = 'Pharmacy'
WHERE DEPARTMENTTYPE IS NULL
  AND RETIRED = 0
  AND BILLTYPEATOMIC LIKE 'PHARMACY_%';

-- Default store-service bills
UPDATE bill
SET DEPARTMENTTYPE = 'Store'
WHERE DEPARTMENTTYPE IS NULL
  AND RETIRED = 0
  AND BILLTYPEATOMIC LIKE 'STORE_%';

---

Step 4 — Sanity check

After running Steps 2–3, verify the specific bill types used in the stock transfer report are clean:

SELECT BILLTYPEATOMIC, DEPARTMENTTYPE, COUNT(*) AS cnt
FROM bill
WHERE BILLTYPEATOMIC IN (
  'PHARMACY_ISSUE', 'PHARMACY_RECEIVE',
  'PHARMACY_TRANSFER_REQUEST', 'PHARMACY_TRANSFER_REQUEST_PRE',
  'PHARMACY_ISSUE_CANCELLED', 'PHARMACY_RECEIVE_CANCELLED',
  'PHARMACY_TRANSFER_REQUEST_CANCELLED'
)
AND RETIRED = 0
GROUP BY BILLTYPEATOMIC, DEPARTMENTTYPE
ORDER BY BILLTYPEATOMIC;

No row should have DEPARTMENTTYPE = NULL.

---

DepartmentType Enum → String Mapping

Both Bill.DEPARTMENTTYPE and Item.DEPARTMENTTYPE store the Java enum name as a string. Use these exact values in SQL:

Enum constant	SQL string value
Pharmacy	'Pharmacy'
Store	'Store'
Lab	'Lab'
Clinical	'Clinical'
NonClinical	'NonClinical'
Opd	'Opd'
Inward	'Inward'
Theatre	'Theatre'
Etu	'Etu'
Kitchen	'Kitchen'
Other	'Other'

Why the Java Admin Function Was Slow

The previous "Fill Bill Department Type" button in admin_functions.xhtml called DataAdministrationController.fillBillDepartmentTypeFromBillItems(), which:

1. Loaded all NULL-departmentType bills into JVM memory with one JPQL query.
2. For each bill, fired a separate JPQL query to fetch its bill items.
3. Updated each bill individually via JPA.

This is an O(n) round-trip pattern — thousands of JPA calls for a large database. The native SQL JOIN-based UPDATE replaces all of that with a single server-side operation.

Notes

• The bill and billitem table names are lowercase on all known HMIS deployments (Linux MySQL).
• Column names are UPPERCASE (DEPARTMENTTYPE, BILLTYPEATOMIC, RETIRED, BILL_ID, ITEM_ID).
• Safe to run multiple times — all WHERE DEPARTMENTTYPE IS NULL guards are idempotent.
• Bills where items span multiple department types (e.g. a stock adjustment bill containing both Pharmacy and Store items) cannot be auto-classified. Review these manually if needed.