wiki:DAPS

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

ColumnFormat
FORENAMES
SURNAME
DOBYYYY-MM-DD
SEXFemale 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

  1. Patient matching
  2. 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.

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"

Error: Macro BackLinks(None) failed
'Environment' object has no attribute 'get_db_cnx'

Last modified 8 years ago Last modified on 09/28/17 17:30:44

Attachments (1)

Download all attachments as: .zip

Note: See TracWiki for help on using the wiki.