While there are several fields that can be decoded on the EBSX02 and EBSX05 to make the information meaningful to non-analysts, the key fields for this analysis are:
REFERRING_ORG_ID - This lists the ODS code of the referring practice. It can be decoded with the EBSX04 ORG_NAME field. The PARENT_ORG_ID can also be pulled from EBSX04 to identify the practice’s clinical commissioning group (CCG).
ACTION_ID - A sequential reference for all actions recorded in e-RS. Used to establish the order in which actions occurred.
ACTION_DT_TM - The date and time that the action occurred. It should be noted that this field is a text field in the format YYYYMMDDHHMMSS and will need to be converted to a usable format to enable analysis.
ACTION_CD - Lists the actions recorded against each UBRN. Decoded using the EBSX03 DISPLAY field.
ORG_ID - Identifies the organisation of the user carrying out the action. It can be decoded with the EBSX04 ORG_NAME field.
ACTION_REASON_CD - Lists the drop-down reason selected in e-RS to give context to actions such as Appointment Cancellations and Referral Triage. Decoded using the EBSX03 DISPLAY field. Not every action will have an action reason.
SERVICE_ID - Identifies the Service with which the referral was associated (if any) when the action was performed in e-RS. Links to the EBSX05 to bring in key information to support the analysis. Recommended fields to link from the EBSX05 are: SERVICE_NAME, PROVIDER_ORG_ID, LOCATION_ORG_ID and SPECIALTY_CD. Not every action will be associated with a Service ID.
UBRN - The Unique Booking Reference Number for the referral. This will enable Operational users to find and manage the referral in the front end of e-RS.
PROVIDER_ORG_ID - Lists the ODS code of the organisation providing the e-RS service. It can be decoded with the EBSX04 ORG_NAME field.
LOCATION_ORG_ID - Lists the ODS code of the site from which the service is provided. It can be decoded with the EBSX04 ORG_NAME field.
SPECIALTY_CD - Identifies the specialty of the e-RS service. Decoded using the EBSX03 DISPLAY field.
Create a new table or query from the extracts containing the fields outlined above, plus the ‘user friendly’ decoded fields, to give the starting point for the analysis (base data).