Pharmacy Stock Take Workflow - hmislk/hmis GitHub Wiki

Pharmacy Stock Take Workflow (End-User Guide)

This guide explains how to perform a pharmacy stock take using snapshot bills, Excel export/import, multiple uploads, and how to view the aggregated variance report. It is written for end users (pharmacy staff, supervisors) and assumes you can access the pharmacy module in the application.

Overview

  • Take a point-in-time snapshot of current stock (by department).
  • Export a guided or blind Excel sheet to conduct the physical count.
  • Enter real stock quantities in Excel and upload them to prepare a physical count bill.
  • Review each upload: matched lines, skipped rows, and counted rows that are not in the snapshot (which you can add as new batches).
  • You can upload multiple times for the same snapshot; nothing is saved until you approve an upload.
  • Approve the physical count when ready to post adjustments to stock.
  • Use the View Variance report to see the initial stock, aggregated variances across all uploads, and the last physical count per item.

Prerequisites

  • You have permissions to access Pharmacy Stock Take pages.
  • You know the Institution and Department for which you are counting stock.

Navigation

  • Stock Take Home: Pharmacy → Adjustments → Stock Take section
  • Create Snapshot: Start New Stock Taking
  • List Snapshots and Actions (View, Upload, Variance): Manage Stock Takings → set the dates → List
  • Upload counts for a snapshot: Upload on the snapshot's row
  • Variance Report for a snapshot: Variance on the snapshot's row

Step 1 — Create a Snapshot

  1. Open Pharmacy Stock Take.
  2. Select the Institution and Department.
  3. Generate snapshot preview and review listed items and quantities.
  4. Click Settle to save the snapshot bill.
  5. Use Print if you need a hard copy reference.

The snapshot bill represents the system’s stock at that point in time. Each item in the snapshot has a unique BillItem ID.

Step 2 — Download Excel

From either the Snapshot print page or the Upload page:

  • Download Guided Sheet: includes system quantities to help counting.
  • Download Blind Sheet: hides system quantities for unbiased counting.

Excel columns include identifying fields such as Item Code, Batch, and the unique BillItem ID. Use that BillItem ID whenever possible.

Step 3 — Perform the Physical Count

  1. Conduct your physical count in the store/ward.
  2. Open the downloaded Excel sheet.
  3. Enter the counted amount in the Real Stock Qty column.
  4. Leave the Real Stock Qty cell blank for items you did not count. Blank means “skip”.
  5. Save your Excel file.

Important behavior:

  • Blank Real Stock Qty is treated as SKIP (not zero). The item is ignored during upload.
  • You can submit partial counts and come back later with more uploads.

Step 4 — Upload the Excel and Review

  1. Open Manage Stock Takings, list your snapshot and click Upload on its row.
  2. Select your completed Excel file and click Process Upload & Review.
  3. The Review Physical Count page opens. A message shows how many rows were:
    • matched to a line of the snapshot,
    • skipped because Real Stock Qty was blank,
    • not in the snapshot.
  4. The first table lists the matched lines: starting quantity, uploaded physical quantity and variance.

The system matches each row by its BillItem ID, so keep that column as it was downloaded.

Rows not in the snapshot

A counted row whose BillItem ID is blank or belongs to no line of this snapshot is not adjusted. Examples are a batch that was never received into this department, an item added to the sheet by hand, or a typing mistake. These rows are listed under Rows not in the snapshot, with the sheet row number, so none disappears unnoticed.

Review page listing rows that are not in the snapshot

Review Physical Count: one matched line, then four counted rows that are not in the snapshot. Two of them can be added as new batches. One has an unknown item code, and one has no batch number.

  • Download as Excel saves the list so the rows can be checked or corrected.
  • The Status column says whether a row can be added:
    • Can be added as a new batch: the item code was found and the row has a batch number and expiry date.
    • Item code not found, No batch number, No or unreadable expiry date or No retail rate: correct the sheet and upload again.
  • Tick Add as new batch for each row you want included. When you approve, the system creates that batch (or reuses it if it already exists for the item with the same batch number and expiry) in this department, and sets its stock to the counted quantity in the same stock adjustment.
  • Rates for a new batch come from the row's Purchase Rate, Retail Rate and Cost Rate columns. If those are blank, the item's most recent batch is used.
  • Expired batches can be added. They then appear in expiry reports and can be discarded through the normal process.
  • Rows you leave unticked are not adjusted.

Step 5 — Approve and Make Stock Adjustments

  • Add any upload comments, then click Approve and Make Stock Adjustments and confirm.
  • The system saves a Physical Count bill linked to the snapshot, creates a Stock Adjustment bill, and sets every line with a non-zero variance (including the ticked new batches) to the counted quantity.
  • A message confirms how many stock lines were updated. If some lines could not be applied, a warning says how many, so you can re-check them before completing the stock take.
  • You can upload again for the same snapshot, for example for areas counted later.
  • Approval requires the PharmacyStockTakeApprove privilege.

Step 6 — View the Variance Report (Read-only)

  1. Go to Manage Stock Takings and list your snapshot.
  2. Click Variance on its row.
  3. The report shows one row per counted snapshot item, followed by one row per batch added from "Rows not in the snapshot":
    • BillItem ID
    • Code, Item, Batch
    • Initial Stock (from snapshot)
    • Sum of Variances (across all saved physical counts for the snapshot)
    • Last Physical Count (from the most recent physical count)
    • Source: Snapshot, or Added (not in snapshot) for a batch added during the stock take. For an added batch, Initial Stock is the department's stock of that batch before it was counted (0 for a new batch).
  4. Use the Print button to print, or Download to export to Excel. Type Added in the Source filter to list only the added batches.
  5. Variance Detailed Report shows the same rows with quantities and values before and after the adjustment. Added batches are included there too.

Variance report filtered to batches added during the stock take

Variance report filtered on Source = Added: batches counted during the stock take that were not in the snapshot, with their initial stock and variance.

Variance Detailed report filtered to added batches

Variance Detailed report, same filter: quantity before and after the adjustment for each added batch. The footer totals cover the whole stock take.

This page is a read-only report. No editing occurs here.

Tips and Best Practices

  • Prefer using the BillItem ID column in Excel to ensure exact matching between the snapshot and your counts.
  • Keep Real Stock Qty blank for items you didn’t count yet; later uploads can complete the picture.
  • Before approving, always check Rows not in the snapshot. Correct typing mistakes and upload again, or tick the rows that are real batches.
  • Use the View Variance report to spot check:
    • Large positive/negative variances
    • Whether all critical items have a recent physical count

Troubleshooting

  • “No snapshot selected” warnings:
    • Navigate from Manage Stock Takings and use the action buttons on the correct row.
  • Upload shows fewer rows than expected:
    • Ensure Real Stock Qty is filled for items you want processed.
    • Ensure the Excel headers remain intact; do not delete or rename BillItem ID, Code, Batch, or Real Stock Qty columns.
  • Items not matching:
    • They are listed under Rows not in the snapshot on the review page.
    • Verify BillItem ID in Excel matches the snapshot's item IDs. A sheet downloaded for a different snapshot will not match.
  • Variance seems duplicated:
    • Remember the Variance Report aggregates all saved physical counts referencing the snapshot.

Data Flow Notes (For Awareness)

  • Snapshot Bill: captures items and quantities at a point in time.
  • Physical Count Bill(s): created when you approve an upload; link back to snapshot via referenceBill.
  • Multiple Physical Counts: allowed per snapshot (there is no forward link from snapshot to a single physical count).
  • Approval: creates a Stock Adjustment bill and updates stock based on non-zero variances.

If you have further questions or need assistance, contact your system administrator or pharmacy IT support.

*** End of Document ***