============================================================
Status (Hermes, 2026-09-21) — research, not a specification; implementation deferred. Matthew's direction:
supply ordering is the next work item once the pharmacy guides finish group C, with implementation after that.
Three things to carry into any build:(1) The per-wholesaler column layouts below are not evidenced. The cited pages were re-fetched 2026-09-21:
two of the AAH links resolve to generic Salesforce portal shells, the Alliance ULM page answers 503, the Phoenix
link is an empty frame page, and the remaining citations are generic store CSV guides — none of them contains the
pip_code,quantity/ProductCode,Quantity/item_id,qty_orderedlayouts asserted here. The real template comes
from the wholesaler's own upload screen, and it should be taken from there before any mapper is written. What
is safe to hold: two columns only (product code + quantity), positive integers, UTF-8 with plain commas, and no
names, pack sizes or prices.(2) Nothing here changes the settled architecture. ERPNext owns the stock ledger and Django holds operating
state (Phase 3, D3.1); the settlement with the wholesaler stays outside the app.(3) The inbound half is on expired_stock. Reading the
wholesaler's delivery file back to fill receipt lines is the same design question in the other direction, and
Matthew has researched it separately. The catalogue gaps an export would hit on the way —
nopip_code, no preferred supplier, and no supplier screen (audit Finding 7) — are recorded against that page and
in the handoff note on agents/handoff/from-hermes.
I understand that the major suppliers have portal in which pharmacy can log into and order stock - can a user import a file, perhaps a csv sheet, that provides details of the required stock?
Added because this page's own tables invite the confusion: the three layouts below name different columns, which
reads as if they name different codes. They do not.
A column label is not a code system. pip_code (AAH), ProductCode (Alliance) and item_id (Phoenix) are the
labels a portal's importer expects in the file. What a portal will accept inside that column — a PIP code, a
GTIN/EAN barcode, or either — is decided by its own template, and the label tells you nothing about which. (And the
specific labels on this page remain unevidenced — see the status block above.)
PIP code — a commercial product code, not an NHS one. Seven digits, allocated per pack (usually written
123-4567, the last digit a check digit). It is owned and maintained in the C+D Data product database: C+D Data
state they "own and maintain a database of over 91,000 pharmacy products" and that a C+D Data subscription "covers
the use of C+D Data within your PMR system and facilitates the ordering of products using PIP codes"
(chemistanddruggist.co.uk, data subscriptions, fetched 2026-09-21). Trade coverage describes it as the 7-digit code
"used by the entire supply chain as the single source of order information", with virtual PIP codes ("V-PIPs") for
generics and specials. So a pharmacy's PIP codes reach it through its PMR system under that licence, and through
the wholesalers' own catalogues, portals and invoices.
GTIN / EAN — the pack's barcode. Allocated by the manufacturer under GS1 and printed on the pack; the FMD 2D
DataMatrix carries the GTIN with the serial number, batch and expiry. A separate identifier from the PIP code, and the
one a till scanner reads.
SNOMED CT (dm+d) — the clinical code, and not part of ordering. dm+d is the NHS dictionary that a prescription's
medication code comes from. NHSBSA describe dm+d as SNOMED CT, the BNF and GTINs — PIP is not part of it
(NHSBSA dm+d page, fetched 2026-09-21). This corrects an earlier statement of mine: dm+d does not carry PIP
codes alongside SNOMED, so PIP and SNOMED are two independent systems whose codes are correlated in practice by the
pharmacy's own systems and the wholesalers' catalogues, not by the NHS dictionary.
What that means for this build.
item_code a portal would not recognise.StockItem.pip_code could ride along in the existing import_snomed_mapping CSV (it already accepts barcode), soWhat the platform already holds, and what it does not (checked 2026-09-21). We hold the SNOMED CT UK
Clinical Extension, not dm+d: uk_sct2cl_42.1.0_20260506000001Z.zip (~930 MB, February 2026 release) was downloaded
by hand and imported into the central dsp_clinic database for the GP app's symptom/diagnosis search —
snomed_concepts 529,392 rows, snomed_descriptions 1,567,909, snomed_relationships 3,548,588, with a pg_trgm
index on descriptions (see snomed-ct-installation).
There is no dm+d / SNOMED CT UK Drug Extension anywhere on the hosts, and no PIP dataset of any kind. That matters
twice over: dm+d is the drug extension (VTM/VMP/AMP/VMPP/AMPP, pack-level, with GTINs), so a download would
give us SNOMED drug codes and barcodes — the clinical mapping and the order file's barcode fallback — and would
not give us PIP codes, which stay the pharmacy's own licensed C+D Data. Phase 3's D3.7 deliberately kept dm+d
out of v1 ("licensing and updates are their own project"), so ingesting it is a decision with its own build, its own
monthly refresh routine and its own licensing check — not a data drop.
Written after a research note proposed acquiring three datasets for "automated teleordering". Two of the three are
fine in principle; one is a licence trap and one we should refuse as designed. Recorded so nobody builds the wrong
thing.
1. dm+d — acquire it, centrally, on release. Confirmed from NHSBSA (fetched 2026-09-21): dm+d and its supporting
files "are released through TRUD", which "provides the distribution and licensing mechanism for NHS
terminologies (including dm+d)". NHSBSA publish a weekly dm+d subpack — XML format, and it explicitly
"includes a mapping file between AMPP and GTIN" (release notes dated 14 and 21 September 2026). There is also the
NHS England Terminology Server with a syndication API. So: register on TRUD, accept the licence, download the
weekly XML, keep one copy centrally (terminology is not client data, and the existing central snomed_* tables are
the precedent), and treat each release as a refresh. Two cautions: the schedule is weekly, not monthly; and an
unattended download needs TRUD credentials held in the platform's secret store, so a documented runbook with the file
fetched on release is the safer shape than a credentialed cron job that can fail silently. What dm+d gives us:
SNOMED drug codes (VTM/VMP/AMP/VMPP/AMPP) and GTINs per pack — the clinical mapping and the order file's barcode
fallback. What it does not: PIP codes.
Update 2026-09-22 — the release is on disk, and nothing reads it yet. Matthew fetched the dm+d XML release by
hand through TRUD (no credentials held anywhere) and it is staged at/srv/datasets/dmd/2026-09-22— 8 files,
228 MB (AMP, AMPP, VMP, VMPP, VTM, GTIN, ingredient, lookup) — with aMANIFEST.txtrecording the provenance,
the SHA-256 of every file and the licence: NHSBSA/TRUD, internal platform use only, do not redistribute,
publish or re-expose the files or derived dictionaries. Two facts to keep alongside the ruling above: the
manifest says the canonical dataset location is not yet decided, and the AMPP records carry a manufacturer
PRICEwith aPRICEDTand aPRICE_BASISCD— a list price, often zero, and not a Drug Tariff
reimbursement rate. No part of the application reads these files: there is no dm+d importer and no dm+d tables
(the pharmacy app's SNOMED item mappings and its reimbursement prices are synthetic demo values), so ingesting dm+d
is still the build this section describes, with its refresh routine. In play for
supply-orders-part3-pricing and its stage 2
consultation.
2. PIP codes — the pharmacy's licensed data, never scraped out of their systems. PIP codes are published under
the C+D Data product database (see the section above: a C+D Data subscription "covers the use of C+D Data within
your PMR system and facilitates the ordering of products using PIP codes"). A research note attributes them to
IQVIA — unverified; C+D Data is the publisher this platform can evidence, and whatever the attribution, the
licence point is the same. Therefore:
3. Wholesaler price lists and stored wholesaler credentials — refuse in that shape. The same research note
proposes holding each pharmacy's AAH/Alliance/Phoenix credentials on the client VPS and running a Celery task that
pulls the wholesaler's catalogue over SFTP. That contradicts a decision this platform settled when the ordering spec
was written (2026-09-21): the app never holds a wholesaler password, never logs into a portal and never scrapes
one — the pharmacy uploads the file itself. Beyond the design decision: it would put our clients' third-party
credentials in our keeping (vault, rotation, audit — on a platform with a credential exposure already open and its
rotation deferred), it presumes wholesaler terms that permit vendor-side automated access, and wholesaler price
files are the pharmacy's commercial relationship with its distributor, not data the vendor should sit in the middle
of. Wholesalers do offer EDI/API integrations to software suppliers, but those come from a contract, not from a
parser: if automation is ever wanted, the route is a commercial conversation with that wholesaler.
==Yes, a user absolutely can.== The major UK pharmaceutical wholesalers explicitly build a native "Quick Add via CSV" or "Bulk Upload" feature directly into their standard web portals (such as AAH Hub, MyAlliancePro, and Phoenix My-PIN).
They provide this feature precisely for pharmacies that want to create an order offline or within an independent software system, allowing the pharmacist to populate a massive web shopping basket in a single click rather than typing lines manually. [1]
To ensure the file uploads without errors, your app's export engine must output a flat, clean CSV or Excel sheet stripped of formatting macros or text styling. The mappers you build inside your client-vps will target two primary columns:
The Product Code (The Identifier): UK wholesalers map items using two specific codes:
123-4567). AAH and Phoenix rely heavily on PIP codes for web basket uploads.5012345678901). Alliance Healthcare accepts both barcodes and PIP codes within their quick-upload arrays. [2, 3]The Order Quantity: A simple integer column defining how many pack units the pharmacy needs.
Your app will output a minimal spreadsheet layout that looks like this:
| ProductCode | Quantity |
|---|---|
| 1234567 | 10 |
| 7654321 | 5 |
By relying on this architecture, the daily checkout cycle is entirely frictionless and takes less than a minute:
[ Your App Inventory Tab ] ──> Clicks "Export AAH Order" ──> Downloads `aah_order.csv`
│
▼
[ Open Wholesaler Web Tab ] ──> Clicks "Upload Order File" ──> Attaches `aah_order.csv`
│
▼
[ Live Portal Check-Out ] ──> Visual review of stock validation ──> Clicks "Confirm"
[ Download AAH Order (CSV) ]. The file drops straight to their computer's downloads folder. [4]To implement this on your architecture (Django 5.2, Python 3.11), your core framework can utilize a clean, memory-efficient data stream directly within the client space, ensuring no sensitive data leaks or lingers on your central platform gateway.
You can implement a standard Django export view using Python's native csv module:
import csv
from django.http import StreamingHttpResponse
from django.views.generic import View
from tenancy.utils import get_current_tenant # Matches your multi-tenant middleware
class WholesalerCSVExportView(View):
def get(self, request, supplier_key, *args, **kwargs):
tenant = get_current_tenant()
# 1. Fetch calculated reorder records from the sovereign tenant database
pending_items = request.user.current_practice.inventory_items.filter(
preferred_supplier=supplier_key,
requires_reorder=True
)
# 2. Set up an in-memory streaming pseudo-buffer to prevent RAM overload
class Echo:
def write(self, value):
return value
pseudo_buffer = Echo()
writer = csv.writer(pseudo_buffer)
# 3. Stream data blocks directly to the browser
def streaming_csv_generator():
# Header Row required by the wholesaler spec
yield writer.writerow(['ProductCode', 'Quantity'])
for item in pending_items:
# Yields clean code loops (e.g., PIP code or EAN barcode)
yield writer.writerow([item.pip_code, item.calculated_order_qty])
response = StreamingHttpResponse(streaming_csv_generator(), content_type="text/csv")
response['Content-Disposition'] = f'attachment; filename="{supplier_key}_order.csv"'
return response
Would you like to examine how to map the inbound delivery processing (getting the batch numbers and expiry dates back into your system via file upload) using this exact same format, or should we look at how to design the front-end template view for the pharmacy's daily ordering dashboard?
[4] https://x-series-support.lightspeedhq.com
Do the suppliers have a specific format that the csv sheet needs to be and if so can you provide details?
==Yes, the suppliers have explicit formatting templates.== If a file varies from these specifications by even a single misplaced comma or an incorrect column name, their portal validators will instantly reject the entire bulk upload. [1, 2]
Because the "Big Three" (AAH, Alliance Healthcare, and Phoenix) operate distinct proprietary codebases, their ingestors expect variations in column headers and product identifier types.
The explicit data layouts and formatting rules your Django app's export engine must map to are structured as follows:
aah_order.csv)The AAH Hub portal relies entirely on PIP Codes (the standard 7-digit UK pharmaceutical index number). Their platform parser accepts a strict, minimal layout: [1]
pip_code, quantity123-4567 must be rendered purely as integers: 1234567). [2]pip_code,quantity
1234567,12
7654321,5
alliance_order.csv)The MyAlliancePro layout is more modern and versatile. Because Alliance acts as the exclusive UK distributor for several massive global medicine brands, their file layout allows for international barcode validation: [3, 4]
ProductCode, Quantity (PascalCase is preferred by their web uploader).ProductCode column accepts either the standard 7-digit PIP code or the 13-digit EAN/GTIN barcode structure.5.01234E+12 instead of 5012345678901).ProductCode,Quantity
5012345678901,24
3456789,10
phoenix_order.csv)The Phoenix My-PIN portal functions similarly to AAH but requires explicit mapping flags to differentiate between standard pharmaceutical lines, OTC counter items, and surgical appliances: [5]
item_id, qty_ordereditem_id column. If a PIP code begins with a zero (e.g., 012-3456), the zero must be preserved in the CSV text output (0123456). If your exporter strips the leading zero, their verification layer will flag the item as an unknown code error.item_id,qty_ordered
0123456,6
7891234,20
To ensure that downloading these templates feels completely effortless to independent pharmacists, you must implement three global constraints inside your local parsing code: [6]
UTF-8 encoding with plain comma separation (do not use semi-colons or tabs, as basic European mainframes can trip over regional delimiters). [1]Product Name, Pack Size, or Wholesaler Price. The file must contain the two specified columns and nothing else. The portal will look up the product names internally post-upload. [1, 2, 7]> 0). If your database router passes a zero value or a blank field, the wholesaler's validator will abort the upload. [1, 2]Would you like to examine how to structure the Django multi-database properties so your local practice inventory maps the pip_code, gtin_barcode, and leading_zero logic perfectly across your multi-tenant models?
I can show you how to write the specific Python dictionary mappers for each of these three wholesalers to ensure your code exports the correct column headers dynamically. Would you like to review the backend code implementation?
[1] https://docs-shpf.bsscommerce.com
[3] https://ulm.alliance-healthcare.co.uk
[4] https://www.mbm-omega.co.uk
[6] https://www.parcelmonkey.com
Sure, show me how to write the specific Python dictionary mappers for each of these three wholesalers
To implement this cleanly inside your core codebase—incorporating the precise column naming conventions, identifier selections, and leading-zero formatting rules—you can build a unified Wholesaler Export Factory.
By utilizing a Python dictionary-driven mapping system, you isolate the legacy requirements of AAH, Alliance, and Phoenix into clean configuration definitions. This structure allows your local Django client-vps to dynamically generate compliant CSV files using standard, zero-allocation Python streams.
You can define this configuration mapping schema directly inside an architectural utility file on your stack (e.g., clinical_data/integrations/wholesaler_mappers.py).
# clinical_data/integrations/wholesaler_mappers.py
def format_phoenix_id(item):
"""Phoenix requires a strict 7-digit string, preserving leading zeros."""
if not item.pip_code:
return ""
# Strips any accidental spaces/dashes and pads to 7 digits
clean_code = str(item.pip_code).replace("-", "").strip()
return clean_code.zfill(7)
def format_alliance_id(item):
"""Alliance prefers the 13-digit GTIN barcode, falling back to PIP code if missing."""
if item.gtin_barcode:
return str(item.gtin_barcode).strip()
if item.pip_code:
return str(item.pip_code).replace("-", "").strip()
return ""
def format_aah_id(item):
"""AAH requires a raw numeric PIP code string without dashes."""
if not item.pip_code:
return ""
return str(item.pip_code).replace("-", "").strip()
# The Master Dictionary Configuration Matrix
WHOLESALER_MAPPERS = {
"AAH": {
"headers": ["pip_code", "quantity"],
"id_formatter": format_aah_id,
"filename_suffix": "aah_order.csv",
},
"ALLIANCE": {
"headers": ["ProductCode", "Quantity"],
"id_formatter": format_alliance_id,
"filename_suffix": "alliance_order.csv",
},
"PHOENIX": {
"headers": ["item_id", "qty_ordered"],
"id_formatter": format_phoenix_id,
"filename_suffix": "phoenix_order.csv",
}
}
Next, we look at how to hook these dictionary configurations into a high-performance, multi-tenant Django View. This matches your ImportBase layout philosophy by utilizing a Streaming Response to ensure the client-vps doesn't hit memory limits when handling large orders.
# clinical_data/views.py
import csv
from django.http import StreamingHttpResponse, Http404
from django.views.generic import View
from django.contrib.auth.mixins import LoginRequiredMixin
from tenancy.utils import get_current_tenant # Resolves local client scope
from .integrations.wholesaler_mappers import WHOLESALER_MAPPERS
class SovereignOrderExportView(LoginRequiredMixin, View):
"""
Generates a supplier-specific CSV order file dynamically out of the
sovereign tenant database instance. Cost to core infrastructure: £0.00.
"""
def get(self, request, wholesaler_key, *args, **kwargs):
# 1. Normalize key to match our mapper dictionary keys
supplier_key = str(wholesaler_key).upper().strip()
if supplier_key not in WHOLESALER_MAPPERS:
raise Http404("Unsupported wholesaler mapping format requested.")
mapper = WHOLESALER_MAPPERS[supplier_key]
tenant = get_current_tenant() # Local context resolution
# 2. Query pending stock lines from this isolated tenant context database
# (Assuming custom stock tracking labels map to your preferred_supplier values)
pending_items = request.user.current_practice.inventory_items.filter(
preferred_supplier=supplier_key,
requires_reorder=True,
calculated_order_qty__gt=0
).only('pip_code', 'gtin_barcode', 'calculated_order_qty')
# 3. Establish an in-memory streaming pseudo-buffer
class CSVBuffer:
def write(self, value):
return value
buffer_instance = CSVBuffer()
writer = csv.writer(buffer_instance, delimiter=',', quoting=csv.QUOTE_MINIMAL)
# 4. Stream data directly to the pharmacist browser tab
def csv_row_generator():
# Inject custom wholesaler column headers
yield writer.writerow(mapper["headers"])
# Extract and loop through database fields dynamically
for item in pending_items:
# Execute the specific dictionary formatter block
formatted_id = mapper["id_formatter"](item)
if formatted_id: # Skip line if no valid tracking code exists
yield writer.writerow([formatted_id, item.calculated_order_qty])
# 5. Compile secure response stream
response = StreamingHttpResponse(csv_row_generator(), content_type="text/csv")
response['Content-Disposition'] = f'attachment; filename="{mapper["filename_suffix"]}"'
response['X-Content-Type-Options'] = 'nosniff' # Enforces strict security compliance
return response
To tie this straight into your frontend dashboard templates, add the clean slug routing structure into your multi-tenant URL patterns:
# clinical_data/urls.py
from django.urls import path
from .views import SovereignOrderExportView
urlpatterns = [
# E.g., /inventory/export/aah/ or /inventory/export/alliance/
path('inventory/export/<str:wholesaler_key>/', SovereignOrderExportView.as_view(), name='wholesaler_csv_export'),
]
WHOLESALER_MAPPERS dictionary array on line 26, run tests, and push a minor update.Would you like to examine how to structure the frontend dashboard templates to handle the file download UX smoothly, or look at how to model the inbound verification process when pharmacies receive digital invoices back?
If you're interested, I can help you with:
leading_zero and zfill formatting logic passes NHS data sanity checks.Let me know how you'd like to proceed with the code implementation.