= DAPS

Tags: [[Lcbru Software Category]]

DAPS takes a sub-set of patient details and validates them against UHL PMI and the Spine [[Demographics Batch Service]]

To submit a file for processing with DAPS, copy a CSV file with the correct columns (see below) into the folder {{{\\uhldwh\BRICCS_DBS_IN}}}.

Files that are not processed get stored in the directory `\\uhldwh\P\DataForImport\Demographic Batch Service\DBS-BRICCS\ERROR`

When processing is complete DAPS place 4 files for each submitted CSV in the folder {{{V:\Study specific recruitment projects\IT returned files\}}}

I think that the files returned from the spine are in the folder {{{\\uhlieaae01\DBSClient\Data\OUTBOX\DBS\RWE\Processed-Auto}}}.  Maybe we could process them from there!

== Input File Format

The input file has to have the following columns

||=Column=||=Format=||
||FORENAMES|| ||
||SURNAME|| ||
||DOB||YYYY-MM-DD||
||SEX||Female or Male||
||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.


== Returned File Format

Files returned from the DBS are CSV files with Linux end of row delimiters and the following columns:
{{{
"Record Type","Local PID","Number of Multiple Matches","Trace Results NHS Number","Date of Birth","Date of Death","Old NHS Number","New NHS Number","Surname","Previous/Alternative Surname","First Forename","Previous/Alternative Forename","Sex","Address Line 1","Address Line 2","Address Line 3","Address Line 4","Address Line 5","Postcode","Previous Address Line 1","Previous Address Line 2","Previous Address Line 3","Previous Address Line 4","Previous Address Line 5","Previous Postcode","Registered GP","Registered GP Practice","Previously Registered GP","Previous Registered GP Practice","Returned Date of Birth","Date Field Last Modified","Returned Current Posting","Date Field Last Modified","Returned Date of Current Posting","Date Field Last Modified","Returned Surname","dflm","Returned First Forename","dflm","Returned Previous/Alternative Surname","dflm","Returned Other Forenames","dflm","Returned Sex","dflm","Returned Address Line 1","dflm","Returned Address Line 2","dflm","Returned Address Line 3","dflm","Returned Address Line 4","dflm","Returned Address Line 5","dflm","Returned Postcode","dflm","Returned Registered GP","dflm","Returned registered GP Practice","dflm"

}}}

[[BackLinks]]