16 Validation and Derivation Procedures

16.1.1.1 Simple Validation Procedure

Is a patient's diastolic blood pressure greater than or equal to his systolic blood pressure?

A.BP_DIASTOLIC >= A.BP_SYSTOLIC

The system tests whether the diastolic is greater than or equal to the systolic blood pressure, and creates a discrepancy if it is.

This Procedure references responses to a Question Group that includes the blood pressure-related Questions BP_DIASTOLIC and BP_SYSTOLIC. Within the Procedure, that Question Group has an alias of "A." The statement above is the expression in a Procedure detail.

16.1.1.2 Validation Procedure with an Arithmetic Function

Is the difference between a patient's systolic blood pressure and his diastolic blood pressure less than 20?

A.BP_SYSTOLIC - A.BP_DIASTOLIC < 20

The system subtracts the diastolic blood pressure from the systolic and then checks if the difference is less than 20; if it is, a discrepancy is created.

Because this Procedure references the same Questions as the example Procedure above, you may want to include both tests in a single Procedure as separate details. If both tests evaluate to True, the system creates two discrepancies against the same data.

16.1.1.3 More Complex Validation Procedure

This Validation Procedure compares the reported onset date and stop date for an adverse event. If the reported onset date of the adverse event is later than the reported stop date, the Validation Procedure creates a discrepancy.

In this example, the Procedure operates on a single event and a single DCM. The Questions are both in the same Procedure Question Group, which is the Question Group ADVERSE_EVENTS. The first Question is AE_ONSET_DATE, and the second is AE_STOP_DATE.

Instead of directly comparing the values of the date Questions, you can create two user variables to hold those values after you have converted them to Oracle date type so they can simply be subtracted. For example, create user variable i to define the variable ONSET_DATE, and j to define the variable STOP_DATE. You must also define pre-detail custom code to convert the date character values stored in Oracle Clinical to Oracle variables of type DATE (for example, convert June 30, 2005 to 20050630). The custom code can also check for invalid dates (such as June 31, 2005) and take care of a type DATE of length 6 (such as June 2005, or 200506, to which a mid-month TO_DATE of 15 might be consistently appended for a length 8-type DATE of 20050615).

Finally, the Validation Procedure tests ONSET_DATE > STOP_DATE (for example, 20050630 > 20050603) and creates a discrepancy if True. The user variables are used in the actual comparison, not the underlying raw data values. The purpose of the user variables and the pre-detail custom code is to create variables of type DATE so that this simple numerical comparison can be effected.

No system variables are used by this Procedure definition.

16.4.1.1 Patient Information Cursors

The Patient Information Cursor becomes the outermost loop in the Procedure. The system generates a standard Select statement for the Patient Information Cursor so that it always fetches values for the same columns. The cursor retrieves patient records sequentially, putting the information into standard variables such as Clinical Study ID and Patient Position ID. See "Standard Variables" for a complete list.

16.4.1.2 Procedure Question Group Cursors

Procedure Question Group (Procedure QG) Cursors are nested inside the Patient Cursor. The system creates one Procedure QG Cursor for each Procedure Question Group, and generates a Select statement for it based on your Procedure Question Group definition (see "Defining Procedure Question Groups"). The Procedure QG Cursors retrieve key values, Question responses, and response-related information (entry timestamp, response ID, and exception value text).

The system puts the values of the key fields and response-related information into Question Group variables (see "Question Group Variables") and puts Question response values into Question variables (see "Question Variables").

16.4.2.1 Example: Procedure with More Than One Procedure Question Group

The structure can be represented as follows:

/* cursor for getting VITALS Production */
cursor V_CUR(
ORDER BY RDCM.VISIT_NUMBER ASC,RDCM.SUBEVENT_NUMBER ASC, RES.REPEAT_SN ASC;
V V_CUR%rowtype;
V$BEGIN_SEQNUM  number := -99999;
V$END_SEQNUM  number := 99999;
/* cursor for getting AE Production */
cursor A_CUR(
...
ORDER BY RDCM.VISIT_NUMBER ASC,RDCM.SUBEVENT_NUMBER ASC, RES.REPEAT_SN ASC;
A A_CUR%rowtype;
A$BEGIN_SEQNUM  number := -99999;
A$END_SEQNUM  number := 99999;
   /****** Main Begin ******/
loop <<fetch_next_patient>>
         /****** Post Patient ******/
 loop << fetch_V_cur>>
            /****** Post QG V ******/
 loop << fetch_A_cur>>
               /****** Post QG A ******/
 <<detail_1>>
 <<detail_2>>
  /****** Post Details ******/
          end loop; /* A cursor loop */
        end loop; /* V cursor loop */ 
 end loop; /* patients cursor loop */
   /****** Main End ******/
 /* Populate discrepancy check table */
  /*  Populate response check table with derived values*/
end;

16.4.2.2 Example: Procedure Containing One Procedure Question Group

A Procedure fetches information as follows when it includes only one Procedure Question Group, and therefore only one Procedure QG Cursor. In this example, the Procedure Question Group is called Physical Exam, and two visits have occurred so far. The sort order is set to the default: by Event and Subevent in ascending order (see "Sort Order").

Retrieve Patient 1 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 1

For each detail, execute expression(s)

Retrieve Patient 2 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 2

For each detail, execute expression(s)

16.4.2.3 Example: Procedure with Multiple DCMs

In this example, the Procedure retrieves information from two DCM Question Groups, Physical Exam and Lab Tests, that are collected at the same visit. The sort order is set to the default: by Event and Subevent in ascending order (see "Sort Order"). Each Question Group has its own Procedure QG Cursor:

Retrieve Patient 1 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 1

Retrieve responses and key values for Visit 1 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 1

Retrieve responses and key values for Visit 1 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve Patient 2 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 2

Retrieve responses and key values for Visit 1 Lab Tests for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 2

Retrieve responses and key values for Visit 1 Lab Tests for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 2

For each detail, execute expression(s)

16.4.2.4 Sort Order

In the Question Groups window you can specify the order in which you want the system to retrieve records within the Procedure Question Group Cursor for each Question Group. Order matters primarily when you use lag variables or perform a similar function yourself by using user variables to hold information from record instance to instance. You may want to compare each visit with the previous visit by Visit and Subevent Order or by DCM Date and Time (which, particularly for subevents, could differ from the Subevent Order). There are two fields for this purpose: Event and Extension.

In the Event field, you can choose to order by either:

• Visit Number and Subevent Number

• DCM Date and Time

The default behavior is: Visit Number ascending order, Subevent Number ascending order.

Within the context you choose in the Event field, in the Extension field you can choose to order by either:

• Repeat Sequence Number (SN) alone (applicable only to repeating Question Groups; the setting has no effect if repeats are not collected)

• Qualifying Value followed by Repeat Sequence Number (applicable only to DCMs with qualifying values; not available in pre-V3.1-style Procedures)

The default value is Response Repeat Sequence Number ascending.

In both fields, you can choose any combination of ascending or descending order.

16.4.2.5 Sample Procedure Code, with Custom Code Locations

Following is more detailed sample Procedure code. The places where you can insert custom code are highlighted. See "Custom Code" for further information.

PROCEDURE main /*-------------------------------------------------------------*/
(i_clinical_study_id         IN patient_positions.clinical_study_id%TYPE,
 i_clinical_study_version_id IN patient_positions.clinical_study_version_id%TYPE,
 i_data_modified_flag        IN patient_positions.data_modified_flag%TYPE,
 ...
 i_current_location          IN patient_positions.owning_location%TYPE,
 i_timer                     IN BOOLEAN := FALSE ) IS
 v_duplicate_discrepancy     BOOLEAN;

/* << Oracle Clinical puts DECLARATION custom code here >> */

BEGIN
 IF i_timer THEN timer.capture; END IF;

BEGIN
  ...
 std.v_code_location := 'Main Begin';

/* << Oracle Clinical puts MAIN-BEGIN custom code here >> */

 OPEN std.patients_cur; 
 LOOP <<fetch_next_patient>>
   FETCH std.patients_cur INTO std.patients_rec; 
   std.v_code_location := 'Post Patient';
   EXIT WHEN std.patients_cur%NOTFOUND;

/* <<Oracle Clinical puts POST-PATIENT custom code here >> */

  IF std.v_debug = 'Y' 
      THEN DBMS_OUTPUT.PUT_LINE (' ');
           DBMS_OUTPUT.PUT_LINE ('--> Patient: '||std.patients_rec.patient);
   END IF;
   BEGIN
     OPEN b_cur(std.patients_rec.patient_position_id,b$begin_seqnum,b$end_  seqnum);
     std.v_code_location := 'Fetching B data';
     FETCH b_cur INTO b;
     IF b_cur%FOUND 
         THEN  b$has_data := 'Y';
          ELSE IF b$has_data = 'Y'  
                         THEN EXIT;
                           ELSE b := null_b_record; 
                     END IF;
     END IF;
     std.v_code_location := 'Post QG B';

/* << Oracle Clinical puts POST-QG-B custom code here >> */

                                ...  std.v_code_location := 'Pre Details';

/* << Oracle Clinical puts PRE-DETAILS custom code here >> */

            <<detail_1>>
            std.v_code_location := 'First prototype detail - 1 = 1 test';
            IF (1 = 1)
                                        ...
            END IF;
            GOTO detail_2; /* NextDetail=detail_2 */
            <<detail_2>>
            std.v_code_location := 'Second prototype detail - 2 = 2 test';
            IF (2 = 2)
                                        ...
            END IF;
            GOTO detail_3; /* NextDetail=detail_3 */
            <<detail_3>>
            std.v_code_location := 'Third prototype detail - 3 = 3 test';
            IF (NOT(c.diasbp IS NULL OR c.sysbp IS NULL OR c$sysbp$l1 IS NULL))
               AND (3 = 3)
               ...
            END IF;
            GOTO no_more_details; /* NextDetail=no_more_details, could omit */
         <<no_more_details>> 
         NULL;
                                  ...
                                  std.v_code_location := 'Post Details';

/* << Oracle Clinical puts POST-DETAILS custom code here >> */

  ...
     END LOOP; /* b_cur */
 END;
 END LOOP; /* Patients cursor */
 CLOSE std.patients_cur;
 IF i_timer THEN timer.show_elapsed; END IF;

std.v_code_location := 'Main End';

/* << Oracle Clinical puts MAIN-END custom code here >> */

EXCEPTION WHEN OTHERS THEN exception_handling(SQLERRM);

END main;

16.5.1.1 Correlation on Event

Use correlation on an event, or visit, to process data collected only for certain events relative to the Event Number of the current record (from Procedure Question Group A). Choose Previous, Actual, or Next Event.

For example, you commonly need to compare responses from different Question Groups only with other responses collected at the same visit. In that case, correlate on the Actual Event. Then, when Question Group B is defined as correlated with Question Group A on the Actual Event, the Procedure runs an expression that compares responses from the two Question Groups only when the response from Question Group B was collected at the same visit as the response from Question Group A. If correlation by Event was not specified, the Procedure would run the expression comparing Question Group A's response to Question Group B's response from every visit.

If you specify correlation on the Previous or Next Event, you specify it in the definition of Procedure Question Group B, but it is from the perspective of Question Group A. For example, if Question Group B is defined as correlating with Question Group A on Event (Next), the Procedure runs the expression only when the response from Question Group B was collected at the visit after the visit of the current response from Question Group A. In other words, if the response from Question Group A was collected during Visit 5, the Procedure runs the expression only for the response collected from Question Group B at Visit 6.

Example: Correlation on Event This example shows the same situation as in "Example: Procedure with Multiple DCMs", except that the two Question Groups are correlated by Event. Therefore the Procedure processes the record from the Lab Tests Procedure Question Group only when it was collected at the same visit as the current Physical Exam record.

Retrieve Patient 1 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 1

Retrieve responses and key values for Visit 1 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 1

(No expressions are executed because the visit number does not match.)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 1

Retrieve responses and key values for Visit 1 Lab Tests for Patient 1

(No expressions are executed because the visit number does not match.)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 1

For each detail, execute expression(s)

Retrieve Patient 2 information

Retrieve responses and key values for Visit 1 Physical Exam for Patient 2

Retrieve responses and key values for Visit 1 Lab Tests for Patient 2

For each detail, execute expression(s)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 2

(No expressions are executed because the visit number does not match.)

Retrieve responses and key values for Visit 2 Physical Exam for Patient 2

Retrieve responses and key values for Visit 1 Lab Tests for Patient 2

(No expressions are executed because the visit number does not match.)

Retrieve responses and key values for Visit 2 Lab Tests for Patient 2

For each detail, execute expression(s)

16.5.1.2 Correlation by Qualifying Value

Use correlation by qualifying value to compare responses to Questions that belong to DCMs with Qualifying Questions, only when the value of the DCM Qualifying Question is the same. The actual qualifying value is not specified, so the system will run the expression over the specified responses whenever their RDCMs' qualifying values are the same.

For example, you might have two DCMs with a qualifying value of either Left or Right, one DCM to collect general information about either the left eye or the right eye, and the other to collect information about abnormalities in one eye or the other. Correlation by qualifying value ensures that left eye general information at each visit is compared only with left eye abnormality information, and right eye general information is compared only with right eye abnormality information.

For information on DCMs with qualifying values, see "Configuring Qualifying Questions".

Example: Correlation by Qualifying Value This example shows a similar situation to "Example: Procedure with Multiple DCMs", except that both Question Groups belong to a DCM with a qualifying value, and are correlated by DCM qualifying value. Therefore the Procedure processes the record from the Lab Tests Procedure Question Group only when its DCM qualifying value is the same as the current Physical Exam record's DCM qualifying value.

Retrieve Patient 1 information

Retrieve responses and key values for V1 Physical Exam for Pat. 1 (DCMQV=Y)

Retrieve responses and key values for V1 Lab Tests for Pat. 1 (DCMQV=Y)

For each detail, execute expression(s)

Retrieve responses and key values for V1 Lab Tests for Pat. 1 (DCMQV=N)

(No expressions are executed because qualifying value does not match.)

Retrieve responses and key values for V2 Lab Tests for Pat. 1 (DCMQV=Y)

For each detail, execute expression(s)

Retrieve responses and key values for V2 Lab Tests for Pat. 1 (DCMQV=N)

(No expressions are executed because qualifying value does not match.)

Retrieve responses and key values for V2 Physical Exam for Pat. 1 (DCMQV=N)

Retrieve responses and key values for V1 Lab Tests for Pat. 1(DCMQV=Y)

(No expressions are executed because qualifying value does not match.)

Retrieve responses and key values for V1 Lab Tests for Pat. 1 (DCMQV=N)

For each detail, execute expression(s)

Retrieve responses and key values for V2 Lab Tests for Pat. 1(DCMQV=Y)

(No expressions are executed because qualifying value does not match.)

Retrieve responses and key values for V2 Lab Tests for Pat. 1 (DCMQV=N)

For each detail, execute expression(s)

Retrieve Patient 2 information--same processing as for Pat. 1 above

16.5.1.3 Correlation by Question Value

When the Procedure compares values from paired sets of Question responses (a Question response common to both the current Question Group and the correlated Question Group), use correlating Questions. If two Question Groups collect data for a common series of nominal times (such as Time Since Dose), you can use the response value of the Time Since Dose Question to allow comparison testing with other Question responses collected at the same nominal time in the correlated Question Group.

Example: Correlation by Question Value The processing of Question Groups correlated on Question Value looks the same as in "Example: Correlation by Qualifying Value". The difference is that the system compares a Question response instead of the DCM qualifying value before running the detail.

16.5.1.4 Restrictions

The following restrictions apply to correlation:

• You can correlate a Question Group only to Question Groups listed above it in the Procedure Question Groups window.

• Correlation and aggregation cannot coexist in a single Procedure.

16.5.1.5 Setting Up Correlation

To use correlation, do the following:

1. In the Procedure Question Groups window, in the Correlate With Alias field, enter the alias of the Question Group with which you want to correlate the highlighted Question Group. The alias must be for a Question Group listed higher in the window than the highlighted Question Group.

2. Enter a value for one or more of the following types of correlation:

   • Event. From the list of values, choose Previous, Actual, or Next. The system will run an expression on a Question response only against the correlated Question Group's response(s) that were collected at the previous, same, or next event, compared to the event of the current Question response.

   • Qualifying Question. Applicable only for Question Groups that belong to DCMs with qualifying values. If selected, the system will run an expression that compares Question responses from Question Groups correlated on Qualifying Value only when the value of both Question Groups' DCM's Qualifying Question is the same.

   • Correlating Question. Click the Correlating Questions button and enter the name of a Question from each of the correlated Question Groups (use the list of values). You can enter one or more pairs of Questions, one from each Question Group. The system will run the Procedure only when the values of each pair of Question responses are the same.

16.5.2.1 Variable Names

The naming convention for the system variables is: alias.question_name[occurrence#]$suffix. The suffixes are: mn, mx, sm, av, and ct for the five functions available. The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

16.5.2.2 Restrictions

The following restrictions apply to aggregate functions:

• A single Procedure cannot include both aggregate functions and correlation.

• Qualifying expressions cannot be applied to an Aggregation Question Group (a Question Group with its Aggregate? box selected).

• All Questions in an Aggregation Question Group must use at least one aggregate function.

• Questions that use aggregate functions cannot use aggregate lag functions (see "Aggregate Lag Functions").

16.5.2.3 Setting up Aggregate Functions

To use aggregate functions, you must:

1. In the Procedure Question Group window, select the Aggregate? box for the Question Group that contains the Question(s) on whose responses you want to run an aggregate function.

2. In the Questions window for that Question Group, for each Question on whose responses you want to run an aggregate function, in the Aggregate Functions field enter each aggregate function you want to have performed. Use the list of values or enter more than one value by entering a value followed by a comma, followed by another value, and so on.

3. From the main Procedures window, click the Details button. The Details window opens.

4. Click the Variables button. The Variables window opens.

5. In a new row in the Variables column, press F9 to open the System Variables window.

6. Select all the aggregate variable(s) you need to use in the Procedure and click OK.

7. Select Report? for each variable whose value you want to be able to see in the Discrepancy Database and in DCFs.

You can use the variables in detail expressions and custom code by pressing F9 from the Expression or Custom Code field and selecting them from the list of values.

16.5.3.1 Variable Names

Oracle Clinical creates two types of system variables for aggregate lag functions:

• Lag Variable. The system creates a lag variable to hold each value needed for the Lag Depth you specify. If you specify a Lag Depth of 3, the system creates three lag variables to hold the values of the three most recent Question responses. These variables follow the naming convention alias.question_name[occurrence#]$Ln. The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

• Aggregate Variable. The system creates a variable for each aggregate function you specify for the Question. The naming convention for the system variables is the same as for the aggregate function: alias.question_name[occurrence#]$suffix. The suffixes are: mn, mx, sm, av, and ct for the five functions available. The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

16.5.3.2 Restrictions on Aggregate Lag Functions

A Question Group cannot support both aggregate functions and aggregate lag functions. If you want to use an aggregate lag function, do not define the Question Group as an aggregate one (do not select its Aggregate? box).

16.5.3.3 Using Aggregate Lag Functions

Do not select the Aggregate? box for the Question Group. That flag is for the aggregate function. See "Aggregate Functions".

To use aggregate lag functions, you must:

1. In the Questions window, for each Question on whose responses you want to run an aggregate function, enter values in the following fields:

   • Lag Depth. Enter the number of responses whose values you want to include in the lag function's calculation. For example, if you enter "5" the system will include the last five responses collected, within the limits you specify in the fields Include Null in Lag? and Include Current Event in Aggregate Lag?.

   • Include Null in Lag? Select this box if you want to include null values in the lag function's calculations. The default value is deselected.

   • Aggregate Lag Function. Enter each aggregate function you want to use. Use the list of values or enter more than one value by entering a value followed by a comma, followed by another value, and so on.

   • Include Current Event in Aggregate Lag? If selected, the aggregate lag function includes the value of the current event in its calculations.

2. In the Details window, click the Variables button.

3. Press F9 to open the System Variables window.

4. Select the aggregate lag variables and click OK.

5. Select Report? for each variable whose value you want to be able to see in the Discrepancy Database and in DCFs.

You can use the variables in expressions and custom code.

16.5.4.1 Variable Names

The naming convention for the system variables is: alias.question_nameoccurrence#$Ln.

The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

16.5.4.2 Setting Up Lag Checks

Do not select the Aggregate? box for the Question Group that contains a Question on whose responses you want to run a lag check.

To create a lag check, you must:

1. In the Questions window, for each Question on whose responses you want to run a lag check, in the Lag Depth field enter the number of responses whose values you want to include in the lag check. For example, if you enter "5" the system will create system variables for the values of the last five responses to the Question for the patient.

2. In the Details window, click the Variables button.

3. Click F9 to open the System Variables window.

4. Select the lag variables and click OK.

5. Use the variables in custom code.

16.5.4.3 Lag Check Examples

If you have a Procedure Question Group called MEDS, alias M, and in it a Question named DOSE, when you specify a Lag Depth of 3, the system creates three lag variables: M$dose$L1, M$dose$L2, and M$dose$L3. In addition, you still have access to the current value of the Question M.DOSE. You could create a discrepancy if the dose did not increase from one visit/repeat combination to the next by making this statement in the test detail: M$DOSE$L1>= M.DOSE. This evaluates to TRUE if the value from the previous visit/repeat combination is greater than or equal to the current visit/repeat, and therefore creates a discrepancy.

The first lag ($L1) of the first repeat of a visit is null. In general, the nth lag of the first n values seen by the system are null.

Other lag check examples:

16.6.2.1 Aggregate Variables

The system creates aggregate variables using the following naming convention:

alias.question_name[occurrence#]$suffix

The suffixes are: mn, mx, sm, av, and ct for the five functions available. The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

16.6.2.2 Lab Attribute Variables

The system creates lab attribute variables using the following naming convention:

question_group_alias$question_name[occurrence#]$suffix

The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

The four two-letter suffixes are:

• hi (high value) Contains the value at the high end of the range for the Question defined in Lab Ranges. The high value variable is data type Number (13,5).

• lo (low value) Contains the value at the low end of the range for the Question defined in Lab Ranges. The low value variable is data type Number (13,5).

• un (unit) Contains the name of the unit used to define the range in Lab Ranges. The unit variable is data type Varchar2 (10).

• nf (Not Found) The system sets this flag to Y if no lab range is found for the age and sex of a patient. If the lab range is not gender- or age-specific, the patient's sex or age is not taken into account. The "not found" variable is data type Varchar2 (1).

16.6.3.1 Standard Variables

The system generates a fixed set of variables for each Procedure. The patient cursor fetches the values for the following patient information fields in the outermost loop of the generated Procedure:

• patient_position_id Internal ID of the patient.

• clinical_study_id Internal ID of the study.

• reported_sex Sex (M or F), from enrollment.

• reported_birth_date Birthdate, from enrollment.

• patient Patient number.

• Site_id Internal ID of site.

• Investigator_id Internal ID of Investigator.

• early_termination_flag *If checked, patient has left study ahead of schedule.

• patient_enrollment_date *Date the patient was allocated this patient position.

• clinical_subject_id Internal ID of clinical subject.

• inclusion_exclusion_date Date of decision to include or exclude the patient from the final analysis.

• reported_death_date I*Date of patient's death.

• reported_patient_reference *Code the patient was known by in a previous study; according to patient (not validated).

• reported_initials * Patient's initials, from enrollment.

• reported_date_last_pregnancy *Date of patients's last pregnancy, from enrollment.

• first_screening_date Patient's first screening date.

• termination_date *Date patient position was terminated.

An asterisk (*) indicates that the variable is available in 3.1-style Procedures only.

The naming convention for standard patient-related variables is rxcpdstd.patients_rec.question name; for example, the Question variable name for the Question REPORTED_BIRTH_DATE would be rxcpdstd.patients_rec.reported_birth_date.

16.6.3.2 Question Group Variables

Three types of automatically generated variables appear when you click the QG Vars button from the System Variables window that you display by pressing F9 from the Expressions field in the Details window, or in the Custom Code window:

16.6.3.3 DVG Variables

Discrete Value Groups (DVGs) are assigned to Questions to define the acceptable responses to the Question. There are three types: Internal, Alpha, and Thesaurus. The system creates variables based on the values of internal and thesaurus DVGs. See "Creating and Using DVGs" for further information.

To see DVG variables, open the System Variables window by pressing F9 from the Expressions field in the Details window, or in the Custom Code window, select a Question variable based on a Question with a DVG, and click the DVG button. The DVG button is grayed out when the selected Question variable is based on a Question that does not have a DVG.

Internal DVG Variables Internal DVG variables are not true variables, but a set of fixed values determined by the DVG, not the Question response. The system creates a variable for each value in the DVG. The variable is simply the short DVG value text surrounded by single quotation marks; for example, if the DVG associated with a Question is YES_NO and the short value for YES is Y, the corresponding variable is 'Y'.

Thesaurus DVG Variables If you select the Include Thesaurus Terms box in the Procedure Questions window for a Question with a thesaurus DVG, the system creates a variable for each of the (up to) three columns specified in the DVG to provide supplementary information about the term (response) from the external decode table specified in the DVG definition. The naming convention is: alias$question_nameoccurrence#$suffix where the suffix corresponds to the Short Label defined for each column. The occurrence number is present only when there is more than one occurrence of the Question in the DCM Question Group.

See "Thesaurus DVGs" for further information.

16.8.1.1 Creating an Entirely New Procedure

Define a new Procedure if your company is a new Oracle Clinical user, or if you are creating a Procedure for a new DCM. See "Naming and Describing a Procedure".

Always create new Procedures in the study, not the Global Library. You cannot generate or test procedures in the Global Library. To add a procedure to the Global Library, you must define and test it within a study (via the Definition subsystem), activate it, and then copy it into the Global Library.

From the Definition menu, select Validation Procedures, and then Procedures, or from Definition, choose Derivation Procedures, and Procedures.

16.8.1.2 Creating a New Version of a Procedure

Use a new version for modest changes in functionality with the option to save discrepancies created by the original version. The new version replaces the current Procedure, which is saved with a status of Retired. Select Create New Vers from the Special menu in the Procedure Definitions window.

16.8.1.3 Modifying an Existing Procedure

From either the Global Library or another study, copy, then modify, an existing Procedure that references the same or functionally equivalent Questions and DCMs. Select either Copy Proc-Study or Copy Proc-GLIB from the Special menu in the Procedure Definitions window, then modify as necessary. To view existing Procedures in detail, from the Glib menu, select DCIs DCMs Procedures, and choose Qry Validation/Derivation Procedures, or from Definition, select Validation (or Derivation) Procedures, then Procedures, then choose a study. The following restrictions apply:

• You can copy only Active Procedures.

• The Data Collection Modules, Questions Groups, and Questions referenced in the Procedure (or their functional equivalents) must exist in the study to which you copy the Procedure; otherwise the copy action is terminated with an error message.

• You can copy only Procedures with domains in the current study domain searchlist.

16.8.3.1 Access Privileges

The navigation option(s) you see under Definition are determined by your access privileges. You can perform the following tasks through these menu options:

16.8.3.2 Defining a Procedure

To define the Procedure, enter the information requested, including:

16.8.4.1 Performance Issues

The choices you make in this window have important implications for the efficiency of the Procedures you create. This section gives tips on how to design your Procedure for optimal performance. For more detailed information, see "How Procedures Work Internally".

Each cursor, or Question Group Alias, is nested inside those declared before it. So for each A record fetched, the B cursor must be opened and closed. To minimize need for cursors and maximize performance, use these design strategies:

• Use correlation whenever possible (See "Correlate With").

  Note:

  You can correlate a Question Group only to Question Groups listed above it in the Question Groups window, so order Question Groups accordingly.

• Use qualifying expressions and Where clause extensions whenever possible in Validation Procedures. You cannot use them in Derivation Procedures. See "Qualifying Expression" and"Where Clause Extension".

• If you need two types of aggregate functions—for example, sum and count—from the same Question Group, use only one cursor. You can specify as many aggregates as you need using one alias for all the Questions in that Procedure Question Group.

• Use Event Range and First/Last Event Only whenever possible.

• Bear in mind the fact that the Procedure QG Cursor fetches aggregate Question Groups first, followed by the primary reference Question Group. The cursor then fetches the remaining Question Groups in the order they appear in the window, which is determined by the order in which you define them.

16.8.4.2 About the Question Groups Window

Be sure to read "Performance Issues" before proceeding.

In order to use a Question response value in a Validation or Derivation Procedure, you must define the Question and its DCM Question Group as part of the Procedure. In the Question Group window you can also define special processing for the Question Group.

When you include a DCM Question Group in a Procedure, you create a reference to it. A single DCM Question Group can be referenced by several Procedure Question Groups in the same Procedure. For example, to compute an average baseline value for a group of DCM Questions and compare baseline values to the latest values at each subsequent visit, you need to create two Procedure Question Groups for that DCM Question Group: one an aggregate Question Group to compute average baseline value; one to compare each subsequent value to the average baseline value.

The window includes the following fields:

16.8.7.1 Variable Name

Name of a user variable, unique within a Procedure; up to 30 characters.

16.8.7.2 Init Alias

Specify when to initialize a variable; if the field is left blank, Oracle Clinical initializes the variable for each new patient; if you specify an alias, the variable is initialized after each fetch of that alias (Question Group) cursor.

16.8.7.3 Data Type

Choose the appropriate value from the list.

16.8.7.4 Length

Specify the maximum length of the value of the variable. If you want to limit the length of a text string, or to cause number rounding, enter the limit here.

16.8.7.5 Decimal Places

Set the precision of the value of a user variable with data type NUMBER.

16.8.7.6 Initial Value

Specify the value to which the user variable is initialized. If you leave this field blank, the user variable initializes to a null value.

16.8.7.7 Report

For pre-V3.1-style Procedures only, select if you want discrepancies found on this variable to be reported. In V3.1-style Procedures you set this in the Detail Variable Settings window. See "Using Variables in V3.1-Style Procedures".

16.8.7.8 Description

Description of the variable, up to 70 characters.

16.8.8.1 Order Number

Enter the order in which you want this detail to be executed in relation to the other details in the Procedure. You can change this number in Provisional Procedures only.

16.8.8.2 Continue If Discrepancy

Select this box if you want the Procedure to continue executing other details if this one finds a discrepancy. If so, specify the number of the detail you want to execute next in the Cont at Detail# field. (This functionality is available only in V3.1-style Procedures.)

16.8.8.3 Test Not Null Only

Select this box if you want the Procedure to execute this detail only if the Question response is not Null.

16.8.8.4 Detail Types

This field is found in the Derivation Procedure window only, since Calculation-type details are used only by Derivation Procedures to calculate derived Question responses. All other details (all details in Validation Procedures and details in Derivation Procedures that do not directly derive data) are of type Test, even if they include arithmetic functions and data manipulations. You can use Test details in a Derivation Procedure to ensure that your data is valid before you calculate the derived response, or to check the calculation after deriving it.

16.8.8.5 Externally Derived

Select this box if the response to this Question is derived by a system outside Oracle Clinical, for example, the Thesaurus Management System (TMS).

16.8.8.6 Failure Type

Specify the severity of a discrepancy generated by the Detail expression in this detail line. The failure type is associated with the discrepancy in the Discrepancy Database. You can change this field only for Provisional Procedures. Select from the list. The choices Thesaurus_PT and Thesaurus_SCT are used by the outdated Thesaurus Management Option (TMO) of Oracle Clinical; TMS does not use them. (These values are set in the installation reference codelist VALIDATION FAILURE TYPE CODE; you may want to set the TMO values to Inactive so they do not appear in this list of values.)

Note: Calculation-type details in Derivation Procedures do not generate discrepancies, and therefore, do not use this field.

16.8.8.7 Description

Free form text, up to 200 characters of mixed case, describing the Detail expression in this detail line of the Procedure. The system enters the text you enter here as the default text in the Message field. You can edit and expand it there.

16.8.8.8 Expression

In Validation Procedures and in Test-type details in Derivation Procedures, the expression is a single Boolean statement that, if true, results in a discrepancy being created. For example, in the test detail A.BP_SYSTOLIC - A.BP_DIASTOLIC < 20, the system subtracts the diastolic blood pressure from the systolic and creates a discrepancy if it is less than 20.

You can see all the variables available for use in this expression (Question, system, and user variables) by pressing F9 from this field. See "Procedure Variables" for further information.

In details of type Calculation in Derivation Procedures, the expression generates the derived response to a derived Question. Constraints for derivation details of type Calculation:

• You must give the Calculation Detail a lower order number than any other details that use the response.

• You can include user-entered (non-derived) Questions as input to a calculation or test detail, but not as the subject or target of the calculation detail.

• The Derivation Procedure must generate a value of the same data type as the derived Question.

16.8.8.9 Message

Free form text, up to 500 characters, that appears as a comment associated with any discrepancies this detail generates. The system enters the Description text as the default message.

16.8.8.10 Discrepancy Code

Select the code to be associated with the discrepancy in the Discrepancy Database according to your company's policy.

16.8.10.1 Variable

The Variable field lists all variables (user, system, and Question) currently selected for the detail. You can add variables to the detail by pressing F9 and making selections from the multipick list of values. It includes all remaining variables available to the Procedure. Question variables are Questions you have selected from the Procedures menu, by selecting Question Groups, and then Questions.

16.8.10.2 Perform Detail Only If...

You can select any combination of the three fields included in this section, each of which is designed to avoid processing inappropriate data.

16.8.10.3 Discrepancy Test

If selected, Oracle Clinical will test any discrepancies found on this variable to determine whether a they are new or the same as an existing one. If the same, the system does not create a new discrepancy. This field must be selected for at least one variable in each detail. If selected, the system automatically selects Report also.

Batch validation processes only changed data, so it does not create duplicate discrepancies. Flag variables whose values you may want to compare between batch validation runs. Do not flag variables—such as Patient Name—that might trigger a discrepancy due to a simple misspelling.

16.8.10.4 Report

If selected, the value of this variable is visible in the Maintain Discrepancy Database window, associated with the discrepancy. In that window, users can choose whether or not to include the value in the Data Clarification Form as well. Be sure to report the value of the Question being tested so that, if its response value triggers the creation of a discrepancy through this Procedure, it will be possible to see the discrepant value in the Discrepancy Database window and in the Data Clarification Form. This field must be selected for at least one variable in each detail.

16.8.10.5 Report Order

If you want the variable to be reported, specify the order in which you want it to appear, in relation to other variables from this detail.

16.8.11.1 Adding Custom Code

To reach the Custom Code window, click Custom Code from the Procedures Definitions window. To add custom code of type Post-QG only, you can also click Custom Code from the Question Groups window.

To add custom code you must:

1. From the Custom Code Location list, choose the place you want to put your code.

2. In the main field, enter the PL/SQL code you want to insert into the generated Procedure code at that location. Press F9 for a list of available variables. You can enter up to 4000 characters.

3. Save.

4. Repeat for each custom code location you need.

5. Generate the Procedure. From the Procedure Definition window, select Generate from the Special menu.

To view the full, generated PL/SQL code of the Procedure, go to Edit Proc in the Special menu. You must use the Procedure with Edit navigation path to reach the Procedures window. If you do not see that path, you need additional privileges to see the code.

If you have the necessary privileges, you can edit the generated code. However, if you ever generate the code again, your edits are lost. Custom code is not lost when you regenerate the Procedure.

16.9.3.1 Testing a Procedure from Definition

To test a Procedure from Definition, do the following:

1. In the Procedure Definition window, highlight the Procedure and select Execute in Test from the Special menu. A dialog box appears.

2. If you want to run the Procedure in Debug mode, which lets you view the individual values as they are validated in the .out file, choose Yes. A message appears, giving you the batch job ID number.

3. To check on the job's progress and see the .out and .log file names, select Batch Jobs from the Action menu.

4. View the results:

   • See the .log and .out files in the procedure output files on the server.

   • See the discrepancies created by the Procedure in the Discrepancy Database by selecting Show Test Discrep from the Special menu in the Procedure Definition window. Alternatively, from the Conduct menu, select Data Validation, then Discrepancy Database.

16.9.3.2 Testing a Procedure from Test a Study

To test a Procedure from Test a Study, from the Definition menu, select Test a Study, then Execute Single Procedure, and follow standard procedure for submitting batch jobs. To view the results:

• See the .log and .out files in the procedure output files on the server.

• See the discrepancies created by the Procedure in the Discrepancy Database by selecting Show Test Discrep from the Special menu in the Procedure Definition window. Alternatively, from the Conduct menu, select Data Validation, then Discrepancy Database.

16.10.1.1 Execute a Single Procedure from the PSUB Window

From the Conduct menu, select Data Validation, then Execute Single Procedure, and do the following:

1. From the list of values, choose the Procedure you want to run.

2. From the list of values, choose the version sequence number of the Procedure you want to run.

3. From the list of values, choose Y to run the Procedure in Debug mode, which lets you view the individual values as they are validated in the .out file. The default value is N.

4. Click Submit Job.

To check on the job's progress and see the .out and .log files, click Job Status.

16.10.1.2 Execute a Single Procedure from the Procedures Window

From the Definition menu, select Validation Procedures or Derivation Procedures, then Procedures (or Prov Procedures or Procedures w Edit; the menu path you see depends on your privileges) and do the following:

1. In the Procedure Definitions window, select the Procedure you want to execute. You can also execute a Procedure from any of the child windows.

2. From the Special menu, choose Execute in Prod (or Execute in Test to run against a test data database). A dialog box appears.

3. If you want to run the Procedure in Debug mode, which lets you view the individual values as they are validated in the .out file, choose Yes. A message appears, giving you the batch job ID number.

To check on the job's progress and see the .out and .log files, open the PSUB form (from the Conduct menu, select Data Validation, then Execute Single Procedure) and click Job Status.

16.10.2.1 Facts About Procedure Execution During Batch Validation

• During batch validation, Oracle Clinical runs all Derivation Procedures before any Validation Procedures.

• Batch validation always executes incrementally; that is, it processes only data that has changed since the last batch validation run.

• Oracle Clinical detects changes made in the definition of the length of a DCM Question and automatically regenerates any Active Procedure that references that Question before executing the Procedure.

  That is, the procedure is regenerated if and when it needs to be executed in a subsequent batch validation run because data has been modified for one or more patients in fields referenced by the Procedure. The Procedure is then run on patients with modified data.

• Whenever a derivation calculation is re-executed, Oracle Clinical compares the resulting value with any existing value for the response. If the new value differs, Oracle Clinical creates a new version of the response and records DATA CHANGE or DERIVATION CHANGE, depending on whether the recalculation resulted from a change to data or to the derivation calculation.

• A study-specific parameter (the study's Second Pass Required option) controls if data that has only completed first-pass entry participates in derivation.

16.10.2.2 Batch Validating TMS Derivation Procedures

Oracle Clinical executes derivation procedures before TMS processing in batch validation runs. This feature enables you to perform the following processes on TMS derivation procedures:

• You can derive a value from TMS into an Oracle Clinical derived Question in the first TMS portion of batch validation, and derive related TMS values for the derived Oracle Clinical Question during the second TMS portion of the same batch validation.

• You can use an Oracle Clinical derivation procedure to derive a value in Oracle Clinical, and send that value to TMS for processing during the second TMS portion of the same batch validation.

These are examples of how this feature can be useful:

• you would like to derive values from two different dictionaries for a single verbatim term.

• You would like to substitute the original collected term with another term, leaving the original response intact; for example, in a historic study or a study conducted by a company you have acquired.

Note that you cannot have more than two levels of derivation for TMS Questions. The TMS code only handles the following scenario:

• TMS parent Question

• Derived Question (This Question is in turn used as a parent Question.)

• Derived Question

You cannot create a fourth derived Question.

See the Oracle Thesaurus Management System User's Guide for more information.

16.10.2.3 Procedures and Frozen-status Patients

Oracle Clinical runs Derivation Procedures even for patients whose status is Frozen; in these cases, recalculation can take place when:

• an existing Procedure is changed

• a new Procedure is created

• an existing, unchanged Procedure is explicitly executed over all patients, and calculation is based on external data that has changed

• a patient was frozen before having new or modified data processed by batch validation

Derivation Procedures containing validation expressions may produce discrepancies for patients with a status of Frozen.

16.11.2.1 Conversion Example

Original, pre-V3.1-style Procedure Pre-Details custom code:

...
SELECT DATA_COMMENT_TEXT
INTO COMMENT_VAR
FROM RESPONSES
WHERE RESPONSE_ID = A.QUESTION1$RESP_ID
AND RESPONSE_TS = A.QUESTION1$ENT_TS;
...

The code after conversion by Oracle Clinical (the same, in this case; if the code had included the variables PATIENTS_REC., TEST_OR_PROD, or BATCH_START_TS, they would have been renamed RXCPDSTD.PATIENTS_REC., RXCPDSTD.V_MODE, or RXCPDSTD.V_CURRENT_BATCH_TS, respectively):

...
SELECT DATA_COMMENT_TEXT
INTO COMMENT_VAR
FROM RESPONSES
WHERE RESPONSE_ID = A.QUESTION1$RESP_ID
AND RESPONSE_TS = A.QUESTION1$ENT_TS;
...

The code after you have made the required edits to account for test and production tables' references:

IF RXCPDSTD.V_MODE = 'P' 
THEN /* PRODUCTION RUN*/
...
SELECT DATA_COMMENT_TEXT
INTO COMMENT_VAR
FROM RESPONSES
WHERE RESPONSE_ID = A.QUESTION1$RESP_ID
AND RESPONSE_TS = A.QUESTION1$ENT_TS;
...
ELSE /* TEST RUN*/
...
SELECT DATA_COMMENT_TEXT
INTO COMMENT_VAR
FROM RESPONSEST
WHERE RESPONSE_ID = A.QUESTION1$RESP_ID
AND RESPONSE_TS = A.QUESTION1$ENT_TS;
...
END IF;

16.12.1.1 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:

         /* 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;

16.12.2.1 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".

Table 16-2 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.

16.12.5.1 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.

16.12.5.2 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.