Viewing Audit History for Changes in Custom Review Requirements

While it is possible to review audit history for those cases where a user explicitly changes the review status of a CRF, the status of a CRF may also change as a result of changes in Custom Review Requirements at the DCI level will appear in the review status history. Only changes at the installation or study level will not appear in the review status history.

To view audit history for changes in the Custom Review requirements, you can query the columns crt_list and allow_crt_rn in the DCIS and DCIS$JN tables. For details about these columns, see the Oracle Clinical Stable Interface Technical Reference Manual.

Example 5-1 Query to find DCIs for which the CRT requirement has changed

column dci_id format 999999999
column allow_crt_rn format a10
column crt_list format a30
set pages 1
set lines 200
select before.dci_id, before.allow_crt_rn, after.allow_crt_rn
  from (select row_number () over (order by JN_TIMESTAMP) r_num, dci_id, allow_crt_rn from dcis$jn) before 
  join (select row_number () over (order by JN_TIMESTAMP) r_num, dci_id, allow_crt_rn from dcis$jn) after 
    on before.R_NUM = after.R_NUM+1 
 where before.dci_id = after.dci_id 
   and before.allow_crt_rn != after.allow_crt_rn;
   
select before.dci_id, before.crt_list, after.crt_list
  from (select row_number () over (order by JN_TIMESTAMP) r_num, dci_id, crt_list from dcis$jn) before 
  join (select row_number () over (order by JN_TIMESTAMP) r_num, dci_id, crt_list from dcis$jn) after 
    on before.R_NUM = after.R_NUM+1 
 where before.dci_id = after.dci_id 
   and before.crt_list != after.crt_list;