Data Dictionary for the Amber Report Pipeline

This document provides a detailed data dictionary for the Amber Report Pipeline. The data dictionary includes:

Output Name - The name of the column or field as showing in outputs.
Internal Name - The code friendly name used in the codebase or database outputs.
Data Type - The type of data stored in the column (e.g., string, integer, date).
Description - A brief description of the data contained in the column.
Source - The source of the data, indicating where it is derived from.
Outputs - The outputs that this column is included in.

Data Dictionary

Output Field Name	Internal Field Name	Data Type	Description	Source	Outputs
Region	upd_region	String	The ODS Region of the provider.	Derived in devices_rap.clean_data.cleanse_master_joined_dataset	AMBER Summary, AMBER Detailed, RED Summary, RED Detailed, NON-MIGRATED Summary, NON-MIGRATED Detailed, Data
Provider Code	der_provider_code	String	The ODS code of the provider.	Master Devices PLCM (also in Provider Codes Lookup)	AMBER Summary, AMBER Detailed, RED Summary, RED Detailed, NON-MIGRATED Summary, NON-MIGRATED Detailed, Data
Provider Name	current_name_in_proper_case	String	The ODS provided name of the provider in proper case.	Provider Codes Lookup	AMBER Summary, AMBER Detailed, RED Summary, RED Detailed, NON-MIGRATED Summary, NON-MIGRATED Detailed, Data
High Level Device Type	upd_high_level_device_type	String	The high-level device type of the device, in the format DEV##	Derived in devices_rap.clean_data.cleanse_master_data	AMBER Summary, AMBER Detailed, RED Summary, RED Detailed, NON-MIGRATED Summary, NON-MIGRATED Detailed, Data
Device Category	description_in_title_case	String	The high-level device type name of the device, in title case	Device Taxonomy	AMBER Summary, AMBER Detailed, RED Summary, RED Detailed, NON-MIGRATED Summary, NON-MIGRATED Detailed, Data
RAG Status	rag_status	String	The RAG status classification of the device.	Exception Report	AMBER Summary, AMBER Detailed, RED Summary, RED Detailed, NON-MIGRATED Summary, NON-MIGRATED Detailed
ZCM Handover Date	handover_date_zcm	Date		Exception Report	AMBER Summary, RED Summary, NON-MIGRATED Summary
VCM Handover Date	handover_date_vcm	Date		Exception Report	AMBER Summary, RED Summary, NON-MIGRATED Summary
Exception Notes	exception_notes	String	Notes explaining exceptions or special circumstances for amber status items.	Derived in devices_rap.exception_notes.create_exception_notes	AMBER Summary
Change from Previous Month	change_from_previous_month	String	Description of changes from the previous month's report.	Derived in devices_rap.summary_tables.calc_change_from_previous_month_column	AMBER Summary
Manufacturer	cln_manufacturer	String	The manufacturer of the device.	Master Devices PLCM	AMBER Detailed, RED Detailed, NON-MIGRATED Detailed, Data
Manufacturer Device Name	cln_manufacturer_device_name	String	The device name as provided by the manufacturer.	Master Devices PLCM	AMBER Detailed, RED Detailed, NON-MIGRATED Detailed, Data
Activity Year	cln_activity_year	Integer	The year of the activity or transaction.	Master Devices PLCM	Data
Activity Month	cln_activity_month	Integer	The month of the activity or transaction.	Master Devices PLCM	Data
Devices Ident	devices_ident	String	Unique identifier for the device record.	Master Devices PLCM	Data
Device Insertion Date	cln_device_insertion_date	Date		Master Devices PLCM	Data
Purchased Device Contract	cln_purchased_device_contract	String	The contract reference for the purchased device.	Master Devices PLCM	Data
Device Serial Number	cln_device_serial_number	String	The serial number of the device.	Master Devices PLCM	Data
Size	cln_size	String	The size specification of the device.	Master Devices PLCM	Data
Quantity	cln_quantity	Integer	The quantity of devices in the transaction.	Master Devices PLCM	Data
Supplier Unit Price	cln_supplier_unit_price	Decimal	The unit price charged by the supplier.	Master Devices PLCM	Data
Commissioner Unit Price	cln_commissioner_unit_price	Decimal	The unit price as recorded by the commissioner.	Master Devices PLCM	Data
Total Cost	cln_total_cost	Decimal	The total cost of the device transaction.	Master Devices PLCM	Data
Commissioner Code	der_commissioner_code	String	The code identifying the commissioning organization.	Master Devices PLCM	Data
NHSE Service Category	der_nhse_servicecategory	String	The NHS England service category classification.	Master Devices PLCM	Data
NHSE Service Line	der_nhse_serviceline	String	The NHS England service line classification.	Master Devices PLCM	Data
GP Practice Code	der_gp_practice_code	String	The code identifying the GP practice.	Master Devices PLCM	Data
High Level Device Type	der_high_level_device_type	String	The derived high-level device type classification.	Master Devices PLCM	Data
Subsidiary Device Type	der_subsidiary_device_type	String	The subsidiary or sub-category device type classification.	Master Devices PLCM	Data
Purchased Device Contract	der_purchased_device_contract	String	The derived purchased device contract reference.	Master Devices PLCM	Data
VAT Charged	der_vat_charged	Decimal	The VAT amount charged on the transaction.	Master Devices PLCM	Data
Attendance Identifier	cln_attendance_identifier	String	Identifier for the attendance or appointment record.	Master Devices PLCM	Data

Sources of Data

The data for the Amber Report Pipeline is sourced from various sources:

Name	Internal Source Name	Description
Master Devices PLCM	master_data	Extract of the last 12 months of device data from the Devices PLCM table
Provider Lookup	provider_codes_lookup	Extract of provider data from the ODS
Exception Report	exceptions	CSV file containing the latest Exception Report data sent over from Finance Team
Device Taxonomy	device_taxonomy	Lookup table containing information about device categories, their commissioning status, and migration status to the NHS Supply Chain