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\

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 script 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.

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'