My Oracle Support Banner

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

Last updated on FEBRUARY 18, 2019

Applies to:

Oracle Clinical Remote Data Capture Option - Version 4.5.3 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

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Cause
Solution
References


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