-
Notifications
You must be signed in to change notification settings - Fork 134
Pharmacy StockHistory Archival
Archiving old StockHistory records keeps the active STOCKHISTORY table lean and fast while preserving full history in a separate STOCKHISTORYARCHIVE table. Archived rows are still queryable through reports that have the Include Archived option.
| Privilege | Grants Access To |
|---|---|
ArchiveOldStockHistory |
StockHistory archive page |
ArchiveOldItemBatch |
ItemBatch archive page |
Assign these privileges in User Management → Privileges before the first run.
- Log in as a system administrator.
- Go to Data Administration (top menu).
- In the left sidebar, under the Data tab, click Archival.
- On the Admin Functions page, find the Archival tab.
- Click Archive StockHistory to open the archive settings page.
| Field | Description | Default |
|---|---|---|
| Retention Period (days) | Rows created more than this many days ago are eligible for archival. | 730 (2 years) |
| Cutoff Date | Calculated automatically from retention days. Rows created before this date will be archived. You can also set it directly. | 2 years ago |
| Batch Size | Number of rows moved per database transaction. Smaller batches reduce lock contention on busy systems. | 2 000 |
| Max Batches Per Run | Safety limit on how many batches a single run will process. Prevents runaway jobs. | 50 |
| Dry Run | When checked, only counts eligible rows — no data is moved. Always run a dry run first. | Checked |
- Enter the number of days in Retention Period (days).
- Click Apply — the Cutoff Date field updates automatically.
- Adjust the cutoff date further if needed.
- Set Retention Period and click Apply to confirm the cutoff date.
- Ensure Dry Run is checked.
- Click Preview Count — the page shows how many rows are eligible.
- Review the count. If it looks reasonable, proceed.
- Uncheck Dry Run.
- Set Batch Size and Max Batches Per Run appropriate for your load:
- For off-peak maintenance: Batch Size 5 000, Max Batches 200.
- For live-hours: Batch Size 1 000, Max Batches 20 (run repeatedly).
- Click Run.
- Confirm the prompt ("Run archive now? …").
- The Last Result panel shows rows archived, batches run, duration, and whether the batch limit was reached.
The result panel shows Reached Batch Limit: Yes. The job stopped before all eligible rows were moved. Simply click Run again — it will continue from where it left off until all eligible rows are moved or the limit is reached again.
| Field | Meaning |
|---|---|
| Mode | Dry run or Live run |
| Candidates | Total rows eligible for archival (based on the cutoff date) |
| Archived | Rows actually moved in this run |
| Batches Run | Number of batches executed |
| Reached Batch Limit | Yes = more rows remain; run again |
| Started / Ended | Wall-clock timestamps |
| Duration (ms) | Total time for the run |
| Message | Summary text |
Reports that support archived data have an Include Archived checkbox. When ticked, the report queries both the live table and the archive table and merges the results.
Reports with Include Archived support:
-
Stock Ledger (DTO) —
reports/inventoryReports/stock_ledger_dto.xhtml -
Closing Stock Report —
reports/inventoryReports/closing_stock_report.xhtml -
Department Stock History —
pharmacy/pharmacy_department_stock_history.xhtml -
Bin Card (DTO) —
pharmacy/bin_card_dto.xhtml
Leave the checkbox unticked for day-to-day reporting; tick it only when you need historical data beyond the retention window.
The v2.7.0 migration script adds three composite indexes to STOCKHISTORYARCHIVE that match the query patterns used by archive-inclusive reports:
| Index | Columns |
|---|---|
idx_stockhistoryarchive_batch_date_retired |
ITEMBATCH_ID, CREATEDAT, RETIRED |
idx_stockhistoryarchive_date_dept_retired |
CREATEDAT, DEPARTMENT_ID, RETIRED |
idx_stockhistoryarchive_historytype_dept_createdat |
HISTORYTYPE, DEPARTMENT_ID, CREATEDAT |
The script is idempotent — safe to run multiple times. Run it through the normal database migration process.
| Frequency | Action |
|---|---|
| Monthly | Run with Dry Run to review candidate count |
| Quarterly | Run live archive during off-peak hours |
| After each run | Confirm Reached Batch Limit = No (or re-run if Yes) |
"Archive run failed" error
Check the Payara server log (server.log) for the full stack trace. Common causes:
- Database connection timeout on a very large batch — reduce Batch Size.
- Insufficient database privileges for the JDBC pool user.
Cutoff date did not change after editing Retention Period Click the Apply button next to the Retention Period field. Simply typing a new value and pressing Tab does not recalculate the cutoff — you must click Apply.
Preview count is 0 but you expect archived rows
Archived rows are already in STOCKHISTORYARCHIVE. The preview counts rows still in STOCKHISTORY that are eligible to be moved. If count is 0, all eligible rows have already been archived.