Creation_ts Is Greater Than Modification_ts When Updating DCI Books (Doc ID 1306567.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle Clinical Remote Data Capture Option - Version: 4.5.3 and later   [Release: 4.5 and later ]
Information in this document applies to any platform.

Symptoms

Customer has found records in the DCI_BOOKS, DCI_BOOK_PAGES, DCI_BOOK_PHYSICAL_PAGES and CLINICAL_PLANNED_EVENTS tables where creation_ts is greater than modification_ts.

This is data quality concern to their auditors, and they would like to get it fixed.

Steps To Reproduce:
-----------------------
1. Create a new DCI Book in any study.
2. Save on DCI book.
3. Wait for a few minutes.
4. Add some DCI Pages and save.
5. Wait for a few minutes.
6. Now activate DCI book.

From the back-end check the creation_ts and modification_ts for the DCI book in the DCI_BOOKS, DCI_BOOK_PAGES, DCI_BOOK_PHYSICAL_PAGES and CLINICAL_PLANNED_EVENTS tables.

The following queries can be run after performing the following:

1. Create a new DCI Book
2. Save the DCI book.
3. Wait a few minutes.

4. Run SQL statement:

Provide book_name and clinical_study_id for the below query:

select to_char(creation_ts,'dd-mon-yyyy-hh24:mi:ss'),
to_char(modification_ts,'dd-mon-yyyy-hh24:mi:ss'),
dci_book_id
from dci_books
where name = '<book_name>'
and CLINICAL_STUDY_ID=<clinical_study_id>;

5. Add two DCI Pages, DCI1 and DCI2 to DCI Book
6. Save
7. Wait a few minutes.
8. Activate DCI Book.
9. Run SQL statement again:

select to_char(creation_ts,'dd-mon-yyyy-hh24:mi:ss'),
to_char(modification_ts,'dd-mon-yyyy-hh24:mi:ss'),
dci_book_id
from dci_books
where name = '<book_name>'
and CLINICAL_STUDY_ID=<clinical_study_id>;

10.To check the journal table, run the query:

select to_char(creation_ts,'dd-mon-yyyy-hh24:mi:ss'),
to_char(modification_ts,'dd-mon-yyyy-hh24:mi:ss'),
dci_book_id,
jn_operation
from dci_books$jn where name = '<book_name>'
and CLINICAL_STUDY_ID=<clinical_study_id>;

11.To check the dci_book_pages and dci_book_physical_pages and its journals,
run the following 4 queries using dci_book_id from the above query.

a.
select to_char(creation_ts,'dd-mon-yyyy-hh24:mi:ss'),
to_char(modification_ts,'dd-mon-yyyy-hh24:mi:ss'),
dci_book_page_id,
dci_id
from DCI_BOOK_PAGES
where DCI_BOOK_ID = <dci_book_id>
and clinical_study_id = <clinical_study_id>;

b.
select jn_operation,
to_char(creation_ts,'dd-mon-yyyy-hh24:mi:ss'),
to_char(modification_ts,'dd-mon-yyyy-hh24:mi:ss') ,
dci_book_page_id,
dci_id
from DCI_BOOK_PAGES$jn
where DCI_BOOK_ID = <dci_book_id>
and clinical_study_id =<clinical_study_id>;

c.
select to_char(creation_ts,'dd-mon-yyyy-hh24:mi:ss'),
to_char(modification_ts,'dd-mon-yyyy-hh24:mi:ss') ,
DCI_BOOK_PAGE_ID,
DCI_BOOK_PHYSICAL_PAGE_ID
from DCI_BOOK_PHYSICAL_PAGES
where dci_book_id = <dci_book_id>
and clinical_study_id = <clinical_study_id>;

d.
select jn_operation,to_char(creation_ts,'dd-mon-yyyy-hh24:mi:ss'),
to_char(modification_ts,'dd-mon-yyyy-hh24:mi:ss') ,
DCI_BOOK_PAGE_ID,
DCI_BOOK_PHYSICAL_PAGE_ID
from DCI_BOOK_PHYSICAL_PAGES$jn
where dci_book_id = <dci_book_id>
and clinical_study_id = <clinical_study_id>;

12. Similarly queries for clinical planned events and its journal, following the steps as in bug:

a.
SELECT clin_plan_eve_id,
created_by,to_char(creation_ts,'dd-mon-yyyy-hh24:mi:ss'),
to_char(modification_ts,'dd-mon-yyyy-hh24:mi:ss'),modified_by
FROM clinical_planned_events
where CLIN_STUDY_ID=<clinical_study_id>
and name like '<name>';

b.
SELECT clin_plan_eve_id,
created_by,to_char(creation_ts,'dd-mon-yyyy-hh24:mi:ss'),
to_char(modification_ts,'dd-mon-yyyy-hh24:mi:ss'),modified_by, jn_operation
FROM clinical_planned_events$jn
where CLIN_STUDY_ID=<clinical_study_id>
and name like '<name>';

You will notice that modification_ts is stored when the DCI book was first saved, but creation_ts is updated at the time of the activation of DCI and therefore is greater. This is incorrect.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms