Find And Fix Script For Patch 4.5.1.76 Is Not Picking All The Records For Patient (Doc ID 1245346.1)

Last updated on DECEMBER 04, 2019

Applies to:

Goal

Customer executed the find and fix scripts for patch 4.5.1.76 in their test environment but it did not update two records for the patient with DCI_BOOK_ID.

They found that there was update in the RECEIVED_DCIS table for these two records after patient transfer and therefore the find condition is not picking these records.

This looks to be the following bug:
<Bug 10040839> - FIND SCRIPT FOR BUG 8925493 (SHIPPED IN 4.5.1.76) DOES NOT DETECT ALL CASES

The output of the below query in <Bug 10040839> returned two records with DCI_BOOK_ID returning NULL:

Select
rdci1.RECEIVED_DCI_ID, rdci2.DOCUMENT_NUMBER,
rdci1.RECEIVED_DCI_ENTRY_TS,rdci2.RECEIVED_DCI_STATUS_CODE,
rdci1.DCI_BOOK_ID, rdci2.DCI_BOOK_ID, rdci2.PATIENT, ds1.NAME, ds2.NAME
from received_dcis rdci1, received_dcis rdci2 , dci_books ds1 ,
dci_books ds2
WHERE rdci1.RECEIVED_DCI_STATUS_CODE = 'REMOVED'
AND rdci2.RECEIVED_DCI_STATUS_CODE != 'REMOVED'
AND rdci1.RECEIVED_DCI_ID = rdci2.RECEIVED_DCI_ID
AND rdci2.END_TS = to_date(3000000,'J')
AND rdci1.clinical_study_id = <STUDY_ID>                 --replace here with clinical study_id
AND rdci1.DCI_BOOK_ID IS NOT NULL
AND rdci2.DCI_BOOK_ID IS NULL
--AND rdci1.end_ts = rdci2.RECEIVED_DCI_ENTRY_TS
AND ds1.DCI_BOOK_ID = rdci1.DCI_BOOK_ID
AND ds2.DCI_BOOK_ID(+) = rdci2.DCI_BOOK_ID;

Can these records be skipped?  If not, how can they update these two records?

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.