= DAPS (Demographics Authentication Processing System)

Tags:

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'
 a. {{{USP_UPDATE_MATCH_1A_NHS_NO_MATCH_WITH_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1A_S_NUMBER_MATCH_WITH_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1A_IPN_MATCH_WITH_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1B_CASENOTE_NUMBER_MATCH_WITH_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1C_S_NUMBER_MATCH_WITH_PREV_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1C_S_NUMBER_MATCH_WITH_PREV_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1D_CASENOTE_NUMBER_MATCH_WITH_PREV_SURNAME_AND_DOB}}}
 a. {{{USP_UPDATE_MATCH_1E_NHS_NO_MATCH_DOB}}}
 a. {{{USP_UPDATE_MATCH_2A_S_NUMBER_MATCH_WITH_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2A_IPN_MATCH_WITH_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2B_S_NUMBER_MATCH_WITH_DOB_AND_FIRST_3_CHAR_OF_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_2B_S_NUMBER_MATCH_WITH_DOB_AND_FIRST_3_CHAR_OF_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_2C_CASENOTE_NUMBER_MATCH_WITH_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2D_CASENOTE_NUMBER_MATCH_WITH_DOB_AND_FIRST_3_CHAR_OF_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_2E_S_NUMBER_MATCH_WITH_PREV_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2E_IPN_MATCH_WITH_PREV_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_2F_CASENOTE_NUMBER_MATCH_WITH_PREV_SURNAME_AND_YEAR_OF_BIRTH}}}
 a. {{{USP_UPDATE_MATCH_3A_S_NUMBER_MATCH_WITH_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3A_IPN_MATCH_WITH_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3B_S_NUMBER_MATCH_WITH_DOB}}}
 a. {{{USP_UPDATE_MATCH_3B_IPN_MATCH_WITH_DOB}}}
 a. {{{USP_UPDATE_MATCH_3C_CASENOTE_NUMBER_MATCH_WITH_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3D_CASENOTE_NUMBER_MATCH_WITH_DOB}}}
 a. {{{USP_UPDATE_MATCH_3E_S_NUMBER_MATCH_WITH_PREV_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3E_IPN_MATCH_WITH_PREV_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_3F_CASENOTE_NUMBER_MATCH_WITH_PREV_SURNAME}}}
 a. {{{USP_UPDATE_MATCH_4A_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_SEX_DOB_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_4B_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_SEX_DOB}}}
 a. {{{USP_UPDATE_MATCH_4C_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_SEX_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_4D_DEMOGRAPHIC_SURNAME_FORENAME_3_CHAR_DOB_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_4E_DEMOGRAPHIC_SURNAME_CHAR_SEX_DOB_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_4F_DEMOGRAPHIC_FORENAME_3_CHAR_SEX_DOB_POSTCODE_OR_ADDR_5_CHAR}}}
 a. {{{USP_UPDATE_MATCH_5A_S_NUMBER_MATCH}}}
 a. {{{USP_UPDATE_MATCH_5A_NHS_NO_MATCH}}}
 a. {{{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.
1. Run the stored procedure {{{USP_UPDATE_DBS_TRACING_FILES_RECEIVED}}} to log that the file has been received.
1. 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.
1. Run {{{USP_UPDATE_DEATHS_DBS_TRACING}}}, which updates the death indicator on the {{{DBS_TRACING}}} table if necessary.
1. 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.
1. 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.
1. 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.
1. Run {{{USP_UPDATE_AE_DBS_TRACING}}} which sets the flags to say if the patient is currently or has recently been in A&E.
1. Run {{{USP_UPDATE_OUTPATIENTS_DBS_TRACING}}} which sets the flag if the patient has had a recent output appointment.
1. 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.
1. Create the output Excel files.
 1. Processed - details from the {{{DBS_TRACING}}} table for all patients for the specific {{{BATCH_ID}}}.
 1. 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.
 1. 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

{{{#!sql
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'

}}}

[[BackLinks]]