DAPS (Demographics Authentication Processing System)

DAPS (Digital Audit of Patients Submission) takes a sub-set of patient details and submits them to the UHL and Spine systems for validation and to provide the additional data.

To submit a file for processing with DAPS (Data Analysis and Partitioning with Spine), copy a CSV file with the correct columns into the folder \\uhldwh\BRICCS_DBS_IN.

When processing is complete DAPS (Death date, Address and Phone number Supplementation) will place 3 files for each submitted CSV in the folder V:\Study specific recruitment projects\IT returned files\

Input File Format

The input file has to have the following columns

Column	Format
FORENAMES
SURNAME
DOB
SEX
POSTCODE
NHS_NUMBER
SYSTEM_NUMBER
ADDRESS1
ADDRESS2
ADDRESS3
ADDRESS4
ADDRESS5
LOCAL_ID

Processing

The processing occurs in 2 stages:

1. Send
2. Reeceive

Send Process

Main database is the DWPATMATCH database on the data warehouse server.

1. Copy values from the CSV file in the \\uhldwh\BRICCS_DBS_IN directory to the DBS_TRACING table with a batch ID

The two following steps take place concurrently. I think this is an error, but it may only be the case in my (RaB) copy and not the live copy

2. Patient matching
3. Export patient details to the file to be sent to the Spine

Patient Matching

1. Copy the values from the DBS_TRACING table to the PMI_DEMOGRAPHICS table.
2. Run the script USP_UPDATE_MATCH_FIELDS. This script runs various matching scripts if successful they update the S Number on the PMI_DEMOGRAPHICS table and assign the record a matching code (the code from the start of the script name). If all these fail, the record is given a code of '6 - NO MATCH'
   1. USP_UPDATE_MATCH_1A_NHS_NO_MATCH_WITH_SURNAME_AND_DOB
   2. USP_UPDATE_MATCH_1A_S_NUMBER_MATCH_WITH_SURNAME_AND_DOB
   3. USP_UPDATE_MATCH_1A_IPN_MATCH_WITH_SURNAME_AND_DOB
   4. USP_UPDATE_MATCH_1B_CASENOTE_NUMBER_MATCH_WITH_SURNAME_AND_DOB
   5. USP_UPDATE_MATCH_1C_S_NUMBER_MATCH_WITH_PREV_SURNAME_AND_DOB
   6. USP_UPDATE_MATCH_1C_S_NUMBER_MATCH_WITH_PREV_SURNAME_AND_DOB
   7. USP_UPDATE_MATCH_1D_CASENOTE_NUMBER_MATCH_WITH_PREV_SURNAME_AND_DOB
   8. USP_UPDATE_MATCH_1E_NHS_NO_MATCH_DOB
   9. USP_UPDATE_MATCH_2A_S_NUMBER_MATCH_WITH_SURNAME_AND_YEAR_OF_BIRTH
   10. USP_UPDATE_MATCH_2A_IPN_MATCH_WITH_SURNAME_AND_YEAR_OF_BIRTH
   11. USP_UPDATE_MATCH_2B_S_NUMBER_MATCH_WITH_DOB_AND_FIRST_3_CHAR_OF_SURNAME
   12. USP_UPDATE_MATCH_2B_S_NUMBER_MATCH_WITH_DOB_AND_FIRST_3_CHAR_OF_SURNAME
   13. USP_UPDATE_MATCH_2C_CASENOTE_NUMBER_MATCH_WITH_SURNAME_AND_YEAR_OF_BIRTH
   14. USP_UPDATE_MATCH_2D_CASENOTE_NUMBER_MATCH_WITH_DOB_AND_FIRST_3_CHAR_OF_SURNAME
   15. USP_UPDATE_MATCH_2E_S_NUMBER_MATCH_WITH_PREV_SURNAME_AND_YEAR_OF_BIRTH
   16. USP_UPDATE_MATCH_2E_IPN_MATCH_WITH_PREV_SURNAME_AND_YEAR_OF_BIRTH
   17. USP_UPDATE_MATCH_2F_CASENOTE_NUMBER_MATCH_WITH_PREV_SURNAME_AND_YEAR_OF_BIRTH
   18. USP_UPDATE_MATCH_3A_S_NUMBER_MATCH_WITH_SURNAME
   19. USP_UPDATE_MATCH_3A_IPN_MATCH_WITH_SURNAME
   20. USP_UPDATE_MATCH_3B_S_NUMBER_MATCH_WITH_DOB
   21. USP_UPDATE_MATCH_3B_IPN_MATCH_WITH_DOB
   22. USP_UPDATE_MATCH_3C_CASENOTE_NUMBER_MATCH_WITH_SURNAME
   23. USP_UPDATE_MATCH_3D_CASENOTE_NUMBER_MATCH_WITH_DOB
   24. USP_UPDATE_MATCH_3E_S_NUMBER_MATCH_WITH_PREV_SURNAME
   25. USP_UPDATE_MATCH_3E_IPN_MATCH_WITH_PREV_SURNAME
   26. USP_UPDATE_MATCH_3F_CASENOTE_NUMBER_MATCH_WITH_PREV_SURNAME
   27. USP_UPDATE_MATCH_4A_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_SEX_DOB_POSTCODE_OR_ADDR_5_CHAR
   28. USP_UPDATE_MATCH_4B_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_SEX_DOB
   29. USP_UPDATE_MATCH_4C_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_SEX_POSTCODE_OR_ADDR_5_CHAR
   30. USP_UPDATE_MATCH_4D_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_DOB_POSTCODE_OR_ADDR_5_CHAR
   31. USP_UPDATE_MATCH_4E_DEMOGRAPHIC_SURNAME_CHAR_SEX_DOB_POSTCODE_OR_ADDR_5_CHAR
   32. USP_UPDATE_MATCH_4F_DEMOGRAPHIC_FORENAME_3_CHAR_SEX_DOB_POSTCODE_OR_ADDR_5_CHAR
   33. USP_UPDATE_MATCH_5A_S_NUMBER_MATCH
   34. USP_UPDATE_MATCH_5A_NHS_NO_MATCH
   35. USP_UPDATE_MATCH_5B_CASENOTE_NUMBER_MATCH

Export Patient

1. Run stored procedure USP_UPDATE_S_NUMBER_AND_INTERNAL_NUMBER_IN_DBS_TRACING_BY_BATCH_ID that updates the S number on the DBS_TRACING table from the PMI_DEMOGRAPHICS table where the match level is not like '%5' (which might never happen anyway).
2. Write the output of the stored procedure USP_OUTPUT_NNSTS_BY_BATCH_ID to the batch file to be sent to the Spine. The stored procedure selects data only from the Patient table in the DWREPO that have a matching S Number from the DBS_TRACING table. That is, all data comes from the Patient table and none from the DBS_TRACING table.
3. Execute the batch file P:\DataForImport\Demographic Batch Service\RUN_Process_NNITS_auto.bat which presumably export file somewhere to be processed.

Receive Process

Again uses the database DWPATMATCH.

1. Copy results in the processed file into DBS_NNITS_RESULTS table.
2. Run the stored procedure USP_UPDATE_DBS_TRACING_FILES_RECEIVED to log that the file has been received.
3. Run the stored procedure USP_UPDATE_IDS_MERGES_DBS_TRACING, which updates the S Number and sets a merged flag on the DBS_TRACING table if the S number used on the DBS_TRACING table has been merged into another S number.
4. Run USP_UPDATE_DEATHS_DBS_TRACING, which updates the death indicator on the DBS_TRACING table if necessary.
5. Run USP_UPDATE_DEMOGRAPHICS_FROM_PMI_TO_DBS_TRACING which updates the name and address fields on the DBS_TRACING table from UHL demographics and sets flags on the DBS_TRACING table if they are different than before.
6. Runs USP_UPDATE_DEMOGRAPHICS_FROM_NSTS_TO_DBS_TRACING which updates the DBS_... name and address fields on the DBS_TRACING table from the DBS_NNITS_RESULTS table and sets a flag if they are different from the existing ones.
7. Runs USP_UPDATE_INPATIENTS_DBS_TRACING which sets the flags to indicate if the patient has an upcoming or has had a recent in patient appointment.
8. Run USP_UPDATE_AE_DBS_TRACING which sets the flags to say if the patient is currently or has recently been in A&E.
9. Run USP_UPDATE_OUTPATIENTS_DBS_TRACING which sets the flag if the patient has had a recent output appointment.
10. Run USP_UPDATE_FUTURE_OUTPATIENTS_DBS_TRACING which sets the flag if the patient has an upcoming out-patient appointment and records some details of it.
11. Create the output Excel files.
    1. Processed - details from the DBS_TRACING table for all patients for the specific BATCH_ID.
    2. Alive - uses the stored procedure USP_DBS_TRACING_MAILING_VIEW to get the details of the patients with the specified BATCH_ID that have NULL for RECENT_INPATIENT_FLAG, RECENT_AE_FLAG, CURRENT_AE_FLAG, CURRENT_INPATIENT_FLAG and PATIENT_DEATH_IND_CHANGED_FLAG, and that have the same post code (spaces removed) from DBS and in the UHL PMI.
    3. Different address - details from the DBS_TRACING table patients with the specified BATCH_ID that have NULL for RECENT_INPATIENT_FLAG, RECENT_AE_FLAG, CURRENT_AE_FLAG, CURRENT_INPATIENT_FLAG and PATIENT_DEATH_IND_CHANGED_FLAG, and that have different post code (spaces removed) from DBS and in the UHL PMI.

Specific Runs

Graphic 2 Extract of S Numbers

WITH demo AS (
SELECT 
        [SYSTEM_NUMBER_CURRENT]
    ,[FORENAMES]
    ,[SURNAME]
    ,[POSTCODE]
    ,[GENDER]
    ,[DATE OF BIRTH]
    ,[BATCH_ID]
    ,[MATCH_DEMOGRAPHIC_LEVEL]
  FROM [DWPATMATCH].[dbo].[DBS_TRACING]
)

SELECT
        male.FORENAMES
        ,male.SURNAME
        ,male.[DATE OF BIRTH]
        ,ISNULL(postcode.POSTCODE, '') [PostCode]
        ,ISNULL(male.SYSTEM_NUMBER_CURRENT, '') [Male_S_Number]
        ,ISNULL(female.SYSTEM_NUMBER_CURRENT, '') [Female_S_Number]
        ,ISNULL(postcode.SYSTEM_NUMBER_CURRENT, '') [Postcode_S_Number]
    ,COALESCE(male.SYSTEM_NUMBER_CURRENT, female.SYSTEM_NUMBER_CURRENT, postcode.SYSTEM_NUMBER_CURRENT, '') [S Number]
    ,CASE WHEN (male.SYSTEM_NUMBER_CURRENT <> female.SYSTEM_NUMBER_CURRENT)
                        OR (male.SYSTEM_NUMBER_CURRENT <> postcode.SYSTEM_NUMBER_CURRENT)
                        OR (female.SYSTEM_NUMBER_CURRENT <> postcode.SYSTEM_NUMBER_CURRENT)
                        THEN 'Yes' ELSE '' END Mismatch
FROM    demo male
LEFT JOIN       demo female ON female.SURNAME = male.SURNAME
                                                and female.FORENAMES = male.FORENAMES
                                                and female.[DATE OF BIRTH] = male.[DATE OF BIRTH]
                                                and female.BATCH_ID = 10930 and female.GENDER = 'f'
LEFT JOIN       demo postcode ON postcode.SURNAME = male.SURNAME
                                                and postcode.FORENAMES = male.FORENAMES
                                                and postcode.[DATE OF BIRTH] = male.[DATE OF BIRTH]
                                                and postcode.BATCH_ID = 10929
WHERE   male.BATCH_ID = 10930 and male.GENDER = 'm'