Reconnect check missing L Tables content query

Data Integrity Checks
Summary:	Complete patients must have entries in all of L_ICD10 table L_CCI_Picklist table L_Labs_Flowsheet table L_Pharm_Flowsheet table
Related:	Quality Assurance queries in CFE, L_Dxs table, L_Como table, L_Labs_Flowsheet table, L_Pharm_Flowsheet table, Sending Patients
Firmness:	hard check
Timing:	complete
App:	Centralized data front end.accdb
Coding:	Reconnect_check_missing_L Tables_content query
Uses L Problem table:	no
Status:	implemented
Implementation Date:	2016-01-21
Backlogged:	No

• Cargo

• SMW

• Categories:  

• form:

This query is automatically run as part of Reconnect CFE and initial error checks.

Investigation Process

Data Processor must:

• In CFE, review other profiles with the same sentDtTm from site location. The concern is that if one profile has missing content in one of the above, other profiles that had changes made and where completed and sent, could be missing any changes collector made to any table above.
• have collector resend data.
  • if data is deleted, the processor to obtain backup from Regional Server ASAP and resend.

Log errors

There are checks in place in CCMDB.accdb that should prevent sending of these. So, if any get through then either those checks are not working, or the problem arises during Sending and not at the source.

Log

2021 -11-04

There may be some problems, or missing items, in this log entry. Sending with a previously sent file still on the laptop would not usually cause this problem, so something else was going on in addition. Discovered the inconsistency in the write-up only 4 months after the issue, so impossible to track down now what went wrong, so just leaving this note instead of trying to correct.

• Problem found that no ICD10 code in Reconnect_check_missing_L Tables_content query from S5 laptop, problem fixed. PTorres 16:55, 2021 November 24 (CST)
• Started when Pam cannot send with Error "Invalid SQL statment..." when sending
• Then found out that there are records with RecordStatus "Complete" after I clicked the Reconnect button it saying Complete Record Status.
• We trouble shoot S5 laptop, found out that she still have some Record Status "Sent" but sent with older send date in CFE but not deleted yet on the laptop.
• Because of those old Sent but not deleted on the laptop, CFE Record Status Vetted became Sent again even though it was Vetted before.
• Before if we have Record Status Complete means sending did not go through completely the sending, and need to be re send, but with this problem not true data sent completely the problem is in the Record Status in CFE already Vetted it became Sent again because their was Sent in the laptop which is not deleted.PTorres 16:16, 2021 November 29 (CST)

2021-08-04

• updated query Reconnect_check_missing_L Tables_content query to no longer look at old L Labs Flowsheet table

2021-07-15

• updated to integrate parts that were in another query, and to always only look at the last 90 days, rather than starting at a set dttm.

2019-08-22

• HSC_SICd-2547 - Found out missing all the L Table's (ICD10,CCI,APACHE,LAB TMP and PHARM). Noticed that the RecordStatus is Blank.

These are the D_ID's that the date stamped never get changed.

• • HSC_SICd-2547 Sent 2019/Aug/15 15:15 - Blanks -(ICD10,CCI,APACHE,LAB TMP and PHARM)
    • The Patient copier button was rolled out 2019-08-01; it would have caused the blank RecordStatus. This was resolved.
  • STB_E6a-939 Sent 2019/Aug/15 15:15 - Complete - (ICD10,CCI,APACHE,and TMP )
  • STB_E6a-1042 Sent 2019/Aug/15 15:15 - Complete - (ICD10,CCI,APACHE,and TMP )
  • STB_E6a-1046 Sent 2019/Aug/15 15:15 - Complete - (ICD10,CCI,APACHE,and TMP )
  • STB_E6a-1061 Sent 2019/Aug/15 15:15 - Complete - (ICD10,CCI,APACHE,and TMP )
  • STB_E6a-984 Sent 2019/Aug/15 15:15 - Complete - (ICD10,CCI,APACHE,and TMP )
  • STB_E6a-1056 Sent 2019/Aug/15 15:15 - Complete - (ICD10,CCI,APACHE,and TMP )
    • PTorres 15:38, 2019 August 22 (CDT)

2019-03-27

• added L_Problems functionality

2019-01-06

• added sendDtTm to query - if it needs to be reviewed each time the query triggers it might as well be there
• removed L_Dxs and L_Como table, and added L_ICD10 and L_CCI_Picklist table