Maintaining Patient Statuses

You must create a Validation Procedure to populate the Patient Statuses table for each study using two packaged procedures provided with Oracle Clinical. Data in the Patient Statuses table is used by the Oracle Clinical Enrollment form and in several reports, including the Patient Enrollment Report.

To populate the table initially, use the RXC_PAT_STAT package. To ensure that the information in the Patient Statuses table reflects any change in status entered in a CRF, use PAT_SYNCH, a function in RXC_PAT_STAT; see Patient Synchronization.

For more information, see:

• Populating the Patient Statuses Table
  
• Patient Synchronization
  
• Example
  
• Pre-Details Custom Code
  
• Alternative Approaches to Error Reporting
  
• Type Coercion and Comparison of Derived Values
  

Populating the Patient Statuses Table

As patients progress through a study, they achieve various statuses, such as ENROLLED and TERMINATED. The status ENROLLED is used by the Enrollment form and reports to define when a patient is considered to be enrolled in a study. All studies must use the status of ENROLLED, and can use as many or as few other statuses as are relevant for tracking patients in that study. Other statuses are user‐defined in the installation reference codelist PATIENT STATUS CODE.

Note:

The ENROLLED status has no effect on the unenrolled patient alert, lab criteria assignment, batch loading enrollment data, or access to studies in Oracle Clinical Remote Data Capture.

Each patient status has associated with it a Status Date—the date the patient achieved the status. So, the Status Date associated with a patient status of ENROLLED is the Enrollment Date; the Status Date associated with the patient status of TERMINATED is the Termination Date. A patient can therefore have several statuses at the same time, one for each status achieved so far:

Patient	Status	Status Date
100	ENROLLED	01092003
100	TERMINATED	12092003
101	ENROLLED	03092003
102	ENROLLED	04092003
102	TERMINATED	10092003
103	ENROLLED	07092003
104	ENROLLED	19092003

You must create a special Validation Procedure in each study to populate the Oracle Clinical Patient Statuses table. In the Pre-Details custom code within the Procedure, use a package, RXC_PAT_STAT provided with Oracle Clinical to insert and delete status codes for each patient. The package includes two functions:

• The CLEAR_STAT function takes as input a patient_position_id, and deletes all existing patient status records for a particular patient: Procedure CLEAR_STAT(PATIENT_ID IN NUMBER)

• The SET_STAT function takes as input several keys for a patient, as well as a Status (code), a Status Date, and a Comment. These status fields are inserted into the patient statuses table as follows:

Procedure set_stat(clinical_study_id in number,
site_id in number,
patient_id in number, 
status in varchar2,
status_date in date,
status_comment varchar2)

You must ensure that the CLEAR_STAT function is executed only once per patient, and that a given status is inserted only once. You can use any of the following techniques:

• Access DCMs that can only have a single occurrence, such as Demography and Termination. This is the approach used in the example below.

• Use only aggregate Procedure Question Groups with the MAX function. Each one returns a single row, so the Pre‐Code executes only once. However, you can not access the DCM_Date in an aggregate Procedure Question Group.

• Define variables to set flags indicating that the CLEAR_STAT Procedure has been called for the patient, to ensure it is called only once. Write the status calculation logic so that a given status can be recorded only once.

For more information , see:

• Example
  

Example

Following is an example of a Procedure to track ENROLLED and TERMINATED statuses along with Enrollment Date and Termination Date, referencing DCMs with a single occurrence. The Enrollment Date is the DCM_DATE of the Demog DCM, and the Termination Date is the Question TERM_DATE asked on the Term DCM. Create the Validation Procedure for synchronizing the statuses and dates between the responses and the Patient Statuses table in the following manner:

Procedures

Define the Validation Procedure itself (see Naming and Describing a Procedure.

Procedure Question Groups

Select two Questions Groups, one from each DCM that contains the Question that affects the patient status: DEMOG and TERM. Select Create Place Holder? for each group. Do not correlate the groups. (See Defining Procedure Question Groups.

Procedure Questions

In the example code below, the alias of the DEMOG Procedure Question Group is D. Select DCM_DATE as its Procedure Question. The alias of the TERM Procedure Question Group is T. Select TERM_DATE as its Procedure Question. (See Defining Procedure Questions.

Procedure Details

Define one Procedure detail with a FALSE Boolean expression to prevent the Procedure from creating discrepancies (for example, 2<1). Leave the other fields in the window alone; no other field is relevant to the purpose of this Procedure.

Variable Settings

Procedure definition rules require that you mark at least one variable of this detail for Report and for Discrepancy Test. Because the expression is false, the Procedure never creates a discrepancy and it does not matter which variable you set.

Custom Code

In the Custom Code window, choose Pre-Detail and enter the following:

         /* clear existing patient statuses */
         /* current patient key is RXCPDSTD.patients_rec.patient_position_id */
         rxc_pat_stat.clear_stat(RXCPDSTD.patients_rec.patient_position_id);

         /* Set enrollment date into patient_status */
         if D$has_data = 'Y' then
            rxc_pat_stat.set_stat(i_clinical_study_id 	/* clinical_study_id */,
               RXCPDSTD.patients_rec.site_id 	/* site_id */,
               RXCPDSTD.patients_rec.patient_position_id 		/* patient_id */,
               'ENROLLED'	/* status */,
               rxc_date.convert(D.DCM_DATE)	 /* date must be converted to native Oracle date format for storage */,
               " "	/* status_comment */);
         end if;
         /* Set termination date into patient_status */
         if T$has_data='Y' then
            rxc_pat_stat.set_stat(i_clinical_study_id 	/* clinical_study_id */,
               RXCPDSTD.patients_rec.site_id 	/* site_id */,
               RXCPDSTD.patients_rec.patient_position_id 	/* patient_id */,
               'TERMINATED' 	/* status */,
               rxc_date.convert(T.TERM_DATE) 	/* date ‐ must be converted to native Oracle date format for storage */,
               " "	/* status_comment */);
         end if;

This logic guarantees that the Termination and Enrollment Dates for each patient in the study will be obtained if they exist. The algorithm is:

loop over each patient{

get the termination date (if any of these CRFs exist)

get the enrollment date (if any of these CRFs exist)

delete all status information for the current patient

if a termination form is present, store the termination status with the termination date

if an enrollment form is present, store the enrollment status with the enrollment date

}

Each Procedure Question Group has Create Placeholder? selected, so that if a patient has no Termination or Enrollment data, the Pre‐Code still executes and the CLEAR_STAT function removes any previous statuses. If an Enrollment record is removed from the system because it was mistakenly logged in, the Enrollment status code is cleared by CLEAR_STAT and not reinserted by SET_STAT.

The test on alias$has_data is present to force the insertion of a status only in the case where a relevant Question contains response data.

Patient Synchronization

The Patient Positions table includes information that may be collected as part of clinical trials data. To ensure that the information in the Patient Positions table reflects updates to a patient's data entered in Data Entry, you can use the packaged function RXC_PAT_STAT.PAT_SYNCH in any Derivation or Validation Procedure.

Since Validation and Derivation Procedures are automatically executed during batch validation whenever a patient's data changes or when a Patient Enrollment Record is modified, the PAT_SYNCH function is also re-executed after any event that might change the consistency between the patient enrollment data and the CRF data. If you access any patient information in other Derivation or Validation Procedures (including through access to lab ranges) you should call the PAT_SYNCH function from a Derivation Procedure executed before any Procedure that uses the information.

The function compares the current values of the Patient Enrollment information with the values passed in and returns results as follows:

• If the values are identical, the function changes nothing and returns SUCCESS.

• If any value in the Patient Positions table is null and the corresponding value passed in is non-null, the function updates the enrollment values in the Patient Positions table to the passed values, sets the Modification TS to the Batch Start TS of the batch validation run, and returns SUCCESS.

• If any value in the Patient Positions table for a given patient is non-null and differs from the corresponding value passed in, and the value passed in is non-null, then the function returns FAILURE. You should set the Report? option for the variable to create a discrepancy.

For more information , see:

• Procedure Definition
  

Procedure Definition

Incorporate the PAT_SYNCH function into your Procedure in the same way as the other two packaged functions (see Example).

• Reference the Question Groups and Questions that collect patient status data. The names of the Questions (and therefore Procedure variables) need not be the same as the arguments to the function or the fields in the Patient Positions table (for instance BIRTH_DATE and DOB).

• Call the function from the Pre-details custom code of the Procedure.

• If necessary, the collected data values should be converted to the data type (for instance, DATE) of the same values in the Patient Positions table.

• Declare user variables to receive the return status and the returned values from the Patient Positions table, and also to act as placeholders in the Procedure call for any input arguments you do not want to update.

• Mark as Report? only those user variables you want to report as part of the discrepancy.

• The input arguments can include both Procedure variables (for instance D.INITIALS) and user variables (for instance TEMP_SEX).

• Do not select Continue If Discrepancy? in the Details window. See Type Coercion and Comparison of Derived Values.

Arguments passed to PAT_SYNCH as nulls are ignored. This means you need not synchronize a particular patient's data if it is not collected. It also means that PAT_SYNCH can be called from several Procedures as long as each Procedure passes in different variables.

For instance, one Procedure could update SEX, BIRTH_DATE, and INITIALS, while another updates FIRST_SCREENING_DATE, ENROLLMENT_DATE, and TERMINATION_DATE (perhaps the same Procedure that records patient status information using the RXC_PAT_STAT package).

The return status of the Procedure can be tested in Procedure Details to enable reports on conflicts. Optionally, a new validation failure severity and/or Procedure type could be used to distinguish this new type of validation failure. See Alternative Approaches to Error Reporting.

maintaining-patient-statuses.html#GUID-B57C38FF-2600-4338-A0B4-2538EA50E6FA__CHDFEHGJ lists the variables you must define in order to pass them as arguments to the functional call, with D_ arguments as the input data values and the P_ arguments as the return patient enrollment values.

Table 16-2 List of Arguments for Procedure Definition

Argument Name	In/Out	Data Type	Comment
test_or_prod	in	varchar2	Indicates whether the Procedure was called in test (T) or production (P) mode
batch_start_ts	in	date	Date/time of beginning of batch run
d_patient_position_id	in	number	Internal ID of Patient
d_reported_first_name	in	varchar2	-
d_reported_last_name	in	varchar2	-
d_reported_sex	in	varchar2	Value must be M or F
d_reported_patient_reference	in	varchar2	-
d_reported_initials	in	varchar2	-
d_reported_birth_date	in	date	-
d_reported_date_last_pregnancy	in	date	-
d_first_screening_date	in	date	-
d_patient_enrollment_date	in	date	-
d_termination_date	in	date	-
d_early_termination_flag	in	varchar2	Value must be Y or N
p_reported_first_name	out	varchar2	-
p_reported_last_name	out	varchar2	-
p_reported_sex	out	varchar2	-
p_reported_patient_reference	out	varchar2	-
p_reported_initials	out	varchar2	-
p_reported_birth_date	out	date	-
p_reported_date_last_pregnancy	out	date	-
p_first_screening_date	out	date	-
p_patient_enrollment_date	out	date	-
p_termination_date	out	date	-
p_early_termination_flag	out	varchar2	-

Example

This Procedure handles synchronization of SEX, BIRTH_DATE, INITIALS, and TERMINATION_DATE.

Validation Procedure: PATIENT_DATA_SYNCH

Procedure Question Group: DEMOGRAPHICS, Alias: D

• Procedure variable: SEX
• Procedure variable: DOB
• Procedure variable: INITIALS

Procedure Question Group: TERMINATION, Alias: T, No correlation, Create Placeholder:

• Procedure variable: TERMINATION_DATE

This structure results in a single execution of the Pre-details custom code for each patient. The execution occurs even if the termination data has not been received.

User Variable Name	Report?	Data Type	Length
d_date_last_pregnancy	n	date	-
d_early_termination_flag	n	varchar2	1
d_first_name	n	varchar2	15
d_first_screening_date	n	date	-
d_last_name	n	varchar2	20
d_patient_enrollment_date	n	date	-
d_patient_reference	n	varchar2	25
enr_birth_date	{1} y	date	-
enr_date_last_pregnancy	n	date	-
enr_early_termination_flag	n	varchar2	1
enr_first_name	n	varchar2	15
enr_first_screening_date	n	date	-
enr_initials	y	varchar2	4
enr_last_name	n	varchar220	-
enr_patient_enrollment_date	n	date	-
enr_patient_reference	n	varchar2	25
enr_sex	y	varchar2	1
enr_termination_date	y	date	-
pat_synch_return	n	varchar2	7
temp_dob	n	date	-
temp_sex	n	varchar2	1
temp_term_date	n	date	-

Pre-Details Custom Code

{2}
if (D.SEX = 'MALE') then TEMP_SEX = 'M';
elsif (D.SEX = 'FEMALE') then TEMP_SEX := 'F';
end if;
{3}
TEMP_TERM_DATE := to_date(T.TERMINATION_DATE, 'YYYYMMDD');
TEMP_DOB := to_date(D.DOB, 'YYYYMMDD);
PAT_SYNCH_RETURN := RXC_PAT_STAT.PAT_SYNCH(
RXCPDSTD.V_MODE,
RXCPDSTD.V_CURRENT_BATCH_TS,
RXCPDSTD.patients_rec.PATIENT_POSITION_ID,
D_FIRST_NAME,
D_LAST_NAME,
{4} 	TEMP_SEX,
D_PATIENT_REFERENCE,
{5} 	D.INITIALS,
TEMP_DOB,
D_DATE_LAST_PREGNANCY,
D_FIRST_SCREENING_DATE,
D_PATIENT_ENROLLMENT_DATE,
TEMP_TERM_DATE,
D_EARLY_TERMINATION_FLAG,
ENR_FIRST_NAME,
ENR_LAST_NAME,
ENR_SEX,
ENR_PATIENT_REFERENCE,
ENR_INITIALS,
ENR_BIRTH_DATE,
ENR_DATE_LAST_PREGNANCY,
ENR_FIRST_SCREENING_DATE,
ENR_PATIENT_ENROLLMENT_DATE,
ENR_TERMINATION_DATE,
ENR_EARLY_TERMINATION_FLAG);

In lines 9, 10, and 11 the internal variable names are: TEST_OR_PROD, BATCH_START_TS, and PATIENTS_REC.Site_ID.

Alternative Approaches to Error Reporting

You can choose to create one or potentially multiple failures using one of these approaches:

• Single Failure
  
• Multiple Failure
  

Single Failure

A single Validation Detail can test the return status variable and issue a single failure covering all inconsistencies. For instance:

Detail 1:

Test: PAT_SYNCH_RETURN = 'FAILURE'

Message: Patient Enrollment information differs from CRF data.

Since the error is a multivariate error, all the potentially differing values are associated with the resulting discrepancy.

Multiple Failure

Multiple Details can test for each potential mismatch and report them individually. For instance, assuming that input values come from the Procedure Question Group with alias D (Demography):

Detail 1:

Test: PAT_SYNCH_RETURN = 'FAILURE' AND D.SEX != ENR_SEX

Message: Patient's Sex differs between Patient Enrollment and the CRF data.

Detail 2:

Test: PAT_SYNCH_RETURN = 'FAILURE' AND D.BIRTH_DATE != ENR_BIRTH_DATE

Message: Patient's Birth Date differs between Patient Enrollment and the CRF data.

Type Coercion and Comparison of Derived Values

Be sure to convert data types before passing them to the PAT_SYNCH function.

If you have not selected Continue If Discrepancy? and an incorrect value is derived, whether a length or a data type error, the Procedure's execution stops. All derivations and validations performed by that Procedure are rolled back to reflect the last correct values. Errors are written to the log file (the batch validation log file or the single procedure execution log file). However, the next time the Procedure is run, it is run for all patients.

Calculation expressions specified by users can be one of three data types: VARCHAR2, NUMBER, and DATE. Derived Questions can be one of four types: CHAR, NUMBER, DATE, and TIME. TIME Questions have a length of 4 or 6, and DATE Questions have a length of 4, 6, or 8 and are stored in YYYYMMDD format. CHAR and NUMBER Questions also have predefined lengths that must be enforced.

The following table shows conversions of Oracle Clinical types to Oracle native types. These CHAR conversions are automatically performed by the Procedure in the Calculation phase. Note that decimal precision on numbers is not enforced nor coerced.

Oracle Clinical Derived Question Type	Oracle Native Type of Derived Expression
	varchar2	number	date
char	Length is checked	Error 20104	Error 20104
number	Error 20104	Length is Error 20104 checked	Error 20104
date	Date is checked in YYYYMMDD format; partial dates allowed	Error 20104	Date is converted to YYYYMMDD format before being stored
time	Time is checked in hhmm or hhmmss format, depending on the length of the varchar2 passed in	Error 20104	Error 20104