Utility description: Field Lot Complaint Trend
Field Lot Complaint Trend Utility — Field Complaint Trend Analysis by Lot
ℹ️ Utility performs statistical analysis of consumer and healthcare professional complaints grouped by manufacturing batches according to 21 CFR Part 211.198 and ICH Q10:
• PPM Calculation: Determination of complaints per million units for objective quality assessment.
• Severity Assessment: Automatic identification of critical complaints related to patient safety.
• Pattern Detection: Identification of batches with abnormally high levels of packaging or efficacy defects.
• Recall Support: Generating basis for Market Recall decision making.
⚠️ IMPORTANT:
• Quantity Distributed data is mandatory for PPM calculation.
• Critical complaints (e.g., lack of efficacy in life-saving drugs) require priority investigation.
Usage:
FieldLotComplaintTrendUtility.exe → demo mode (console output)
FieldLotComplaintTrendUtility.exe input.csv output.json → evaluate your data
Input format:
ComplaintID,BatchNumber,ProductName,ComplaintType,Severity,QuantityDistributed,ComplaintDate
Example:
CMP-001,LOT-A,Drug X,Packaging,Low,100000,2026-01-01
📍 Scope of Application (Usage Where):
• Pharmacovigilance: Monitoring drug safety in the post-registration period.
• Quality Assurance (QA): Annual Product Review (PQR/APR).
• Customer Service: Rapid assessment of problem scale upon complaint receipt.
• Regulatory Reporting: Preparation of data for FDA/EMA reports on quality trends.
— WHY IS THIS NEEDED?
Factory Quality Control (QC) may not detect defects that appear only during long-term storage or improper use.
Analysis of field data allows identifying systemic production issues or formulation instability.
This is critically important for protecting company reputation and, most importantly, patient safety.
⚠️ CRITICAL:
• Critical Complaints: Any complaint threatening life or health automatically moves batch to "Recall Review" status.
• PPM Spikes: Sharp increase in PPM compared to historical average indicates a production incident.
• Cluster Analysis: Grouping complaints by dates may point to a specific raw material batch issue.
• Data Integrity: All complaints must be registered, even if deemed unsubstantiated.
Key features:
• Automatic PPM (Parts Per Million) metric calculation
• Complaint prioritization by severity level
• Identification of top defect categories for each batch
• Generation of signals for CAPA initiation
• Compliance with GMP requirements for complaint handling
Critical parameters:
• Total Complaints: Absolute number of complaints
• PPM: Relative defect level
• Critical Count: Number of high-risk complaints
• Top Category: Most frequent problem type
💡 Usage tips:
1. Data Completeness: Include all complaints in analysis, even those closed as "unconfirmed".
2. Context: Consider sales seasonality when interpreting absolute numbers.
3. Comparison: Compare current batch PPM with annual product average.
4. Feedback: Analysis results must be transferred to production for process adjustment.
5. Documentation: Save reports as part of quality management dossier.
⚠️ Note: This utility is a trend monitoring tool. It does not replace official investigation of each individual complaint but helps identify batches requiring special attention.input.csv
ComplaintID,BatchNumber,ProductName,ComplaintType,Severity,QuantityDistributed,ComplaintDate
CMP-2026-001,LOT-2026-A,Aspirin 100mg,Packaging,Low,100000,2026-05-10
CMP-2026-002,LOT-2026-A,Aspirin 100mg,Packaging,Low,100000,2026-05-12
CMP-2026-003,LOT-2026-B,Insulin Glargine,Efficacy,High,50000,2026-05-15
CMP-2026-004,LOT-2026-B,Insulin Glargine,Appearance,Medium,50000,2026-05-16
CMP-2026-005,LOT-2026-B,Insulin Glargine,AE,Critical,50000,2026-05-18
CMP-2026-006,LOT-2026-C,Paracetamol 500mg,Color,Low,200000,2026-05-20
URS & FS — User Requirements and Functional Specification
This document describes the controlled interface and behaviour of FieldLotComplaintTrendUtility for Field Lot Complaint Trend Utility.
Domain limits and critical parameters
Key fragments from the source description are shown below. Before production use, limits must be verified against the approved specification, registration dossier and local SOPs.
- ℹ️ Utility performs statistical analysis of consumer and healthcare professional complaints grouped by manufacturing batches according to 21 CFR Part 211.198 and ICH Q10:
- • Severity Assessment: Automatic identification of critical complaints related to patient safety.
- • Critical complaints (e.g., lack of efficacy in life-saving drugs) require priority investigation.
- This is critically important for protecting company reputation and, most importantly, patient safety.
- ⚠️ CRITICAL:
- • Critical Complaints: Any complaint threatening life or health automatically moves batch to "Recall Review" status.
- • Data Integrity: All complaints must be registered, even if deemed unsubstantiated.
- • Compliance with GMP requirements for complaint handling
- Critical parameters:
- • Critical Count: Number of high-risk complaints
- 3. Comparison: Compare current batch PPM with annual product average.
- 4. Feedback: Analysis results must be transferred to production for process adjustment.
URS — User Requirements Specification
| ID | Requirement | Criticality | Acceptance criterion |
|---|
| URS-001 | The utility shall accept an input.csv file for Field Lot Complaint Trend Utility with headers defined in the data contract. | High | The file is processed without manual header editing. |
| URS-002 | The utility shall perform deterministic QC evaluation without machine learning and without probabilistic conformance decisions. | High | Identical input data, rule version and configuration produce reproducible results. |
| URS-003 | The utility shall validate mandatory fields, data types, ranges, units and domain plausibility. | High | Schema, conversion and range errors are explicitly reported. |
| URS-004 | The utility shall apply domain limits and rules from the description, approved specification, registration dossier and local SOPs. | High | Each check has PASS/WARNING/FAIL and a clear message. |
| URS-005 | The utility shall generate output.json with machine-readable results, source values, warnings, failures and critical findings. | High | JSON is suitable for LIMS/ELN/MES integration and QA/QC review. |
| URS-006 | The utility shall preserve traceability between batch/sample, input file, applied rules and final status. | High | Output contains identifiers, checked parameters and audit metadata. |
| URS-007 | The documentation shall support IQ/OQ/PQ, CSV/CSA and review by internal QA or inspectors. | Medium | URS, FS, input/output contract and test scenarios are supplied with the utility. |
| URS-008 | The utility shall be used as a QC decision-support tool and not as a substitute for approved specifications and QA/QP release decision. | Medium | Documentation states change control and limit-verification expectations. |
input.csv contract
| # | Field | Type | Sample | Purpose |
|---|
| 1 | ComplaintID | string / controlled vocabulary | CMP-2026-001 | Controlled input parameter for deterministic QC rules. |
| 2 | BatchNumber | string / controlled vocabulary | LOT-2026-A | Batch or lot identifier used for traceability. |
| 3 | ProductName | string / controlled vocabulary | Aspirin 100mg | Product or dosage-form name under evaluation. |
| 4 | ComplaintType | string / controlled vocabulary | Packaging | Controlled input parameter for deterministic QC rules. |
| 5 | Severity | string / controlled vocabulary | Low | Controlled input parameter for deterministic QC rules. |
| 6 | QuantityDistributed | decimal | 100000 | Controlled input parameter for deterministic QC rules. |
| 7 | ComplaintDate | string / controlled vocabulary | 2026-05-10 | Controlled input parameter for deterministic QC rules. |
ComplaintID,BatchNumber,ProductName,ComplaintType,Severity,QuantityDistributed,ComplaintDate
CMP-2026-001,LOT-2026-A,Aspirin 100mg,Packaging,Low,100000,2026-05-10
CMP-2026-002,LOT-2026-A,Aspirin 100mg,Packaging,Low,100000,2026-05-12
CMP-2026-003,LOT-2026-B,Insulin Glargine,Efficacy,High,50000,2026-05-15
Input validation rules
| ID | Field | Rule | Criticality |
|---|
| VR-001 | ComplaintID | The field shall match an approved dictionary or accepted string representation. | High |
| VR-002 | BatchNumber | The field shall match an approved dictionary or accepted string representation. | High |
| VR-003 | ProductName | The field shall match an approved dictionary or accepted string representation. | High |
| VR-004 | ComplaintType | The field shall match an approved dictionary or accepted string representation. | Medium |
| VR-005 | Severity | The field shall match an approved dictionary or accepted string representation. | Medium |
| VR-006 | QuantityDistributed | The field shall match an approved dictionary or accepted string representation. | Medium |
| VR-007 | ComplaintDate | The field shall match an approved dictionary or accepted string representation. | Medium |
FS — Functional Specification
| ID | Function | Implementation |
|---|
| FS-001 | CLI execution | Support execution modes: demo mode without arguments and production mode input.csv output.json. |
| FS-002 | CSV import | Read input.csv in UTF-8/CSV-compatible format and validate header and expected columns. |
| FS-003 | Schema validation | Check mandatory fields, column count, unknown key fields and empty mandatory values. |
| FS-004 | Type conversion | Convert numeric, flag and text values; invalid format is recorded as a row-level error. |
| FS-005 | Domain rule engine | Apply rules for Field Lot Complaint Trend Utility, including critical limits from the description and approved specification. |
| FS-006 | Status aggregation | Produce final status: FAIL for critical failure, WARNING for non-critical deviation, PASS for conformance. |
| FS-007 | JSON export | Write output.json with detailed checks, source values, warnings, failures and critical findings. |
| FS-008 | Audit support | Keep result structure suitable for review, deviation investigation and calculation reproduction. |
| FS-009 | Integration contract | Support the scenario LIMS/ELN/MES → input.csv → utility → output.json → portal/admin review. |
| FS-010 | Error handling | Return explicit messages for missing file, empty CSV, invalid schema, output write failure and invalid format. |
Example output.json
{
"utilityId": "fieldlotcomplainttrendutility",
"utilityFolder": "FieldLotComplaintTrendUtility",
"package": "Vet",
"overallStatus": "PASS|WARNING|FAIL",
"sourceFile": "input.csv",
"processedAtUtc": "2026-06-10T00:00:00Z",
"checks": [
{
"parameter": "ComplaintID",
"value": "CMP-2026-001",
"status": "PASS|WARNING|FAIL",
"message": "Deterministic rule-based check result",
"ruleReference": "FS-RULE-001"
},
{
"parameter": "BatchNumber",
"value": "LOT-2026-A",
"status": "PASS|WARNING|FAIL",
"message": "Deterministic rule-based check result",
"ruleReference": "FS-RULE-002"
},
{
"parameter": "ProductName",
"value": "Aspirin 100mg",
"status": "PASS|WARNING|FAIL",
"message": "Deterministic rule-based check result",
"ruleReference": "FS-RULE-003"
},
{
"parameter": "ComplaintType",
"value": "Packaging",
"status": "PASS|WARNING|FAIL",
"message": "Deterministic rule-based check result",
"ruleReference": "FS-RULE-004"
},
{
"parameter": "Severity",
"value": "Low",
"status": "PASS|WARNING|FAIL",
"message": "Deterministic rule-based check result",
"ruleReference": "FS-RULE-005"
},
{
"parameter": "QuantityDistributed",
"value": "100000",
"status": "PASS|WARNING|FAIL",
"message": "Deterministic rule-based check result",
"ruleReference": "FS-RULE-006"
},
{
"parameter": "ComplaintDate",
"value": "2026-05-10",
"status": "PASS|WARNING|FAIL",
"message": "Deterministic rule-based check result",
"ruleReference": "FS-RULE-007"
}
],
"criticalFindings": [],
"warnings": [],
"audit": {
"inputHash": "sha256:<calculated at runtime>",
"rulesVersion": "<utility executable version>",
"documentation": "FieldLotComplaintTrendUtility.documentation.html"
}
}
Traceability matrix
| URS | FS | Test | Evidence |
|---|
| URS-001 | FS-001, FS-002 | OQ-001 | Verify execution and import of valid input.csv. |
| URS-002 | FS-005, FS-006 | OQ-004 | Repeat the same dataset and compare output.json. |
| URS-003 | FS-003, FS-004, FS-010 | OQ-002, OQ-003 | Verify missing columns and invalid types. |
| URS-004 | FS-005, FS-006 | OQ-004, PQ-001 | Verify critical deviations on real/boundary data. |
| URS-005 | FS-007, FS-009 | OQ-005 | Verify JSON schema and downstream-system suitability. |
| URS-006 | FS-008 | OQ-006 | Verify identifiers and audit metadata. |
| URS-007 | FS-008, FS-010 | IQ-001, OQ-007 | Verify documentation completeness and control evidence. |
| URS-008 | FS-005, FS-008 | PQ-002 | Verify review workflow and no replacement of QA decision. |
IQ/OQ/PQ test scenarios
| ID | Scenario | Expected result |
|---|
| IQ-001 | Verify executable, input.csv, documentation and checksum availability. | Delivery set is complete; version is recorded. |
| OQ-001 | Valid sample row from input.csv. | PASS or acceptable WARNING according to rules. |
| OQ-002 | Remove a mandatory CSV column. | Schema error or FAIL with missing-column reference. |
| OQ-003 | Place a non-numeric value into a numeric field. | Type-conversion error with row/field reference. |
| OQ-004 | Set a critical parameter outside the limit. | FAIL and critical finding. |
| OQ-005 | Verify output.json structure. | All mandatory sections are present and JSON is valid. |
| OQ-006 | Verify batch/sample traceability. | Input and result identifiers match. |
| PQ-001 | Verify 3–5 real user batches/samples. | Result is confirmed by QC/QA review. |
| PQ-002 | Verify deviation workflow and manual QA decision. | Utility supports review but does not replace approved decision. |
QA/QC and change control
- Do not rename columns without updating validator, documentation and test set.
- Retain
input.csv, output.json, executable version and checksum. - Before production use, perform IQ/OQ/PQ or equivalent CSV/CSA verification.
- Critical limits shall be verified against the approved specification, registration dossier and local SOPs.
- The utility provides structured QC decision support; final release decision remains with QA/QP and approved procedures.