In Remote Data Capture, the Home page displays an Activities section that presents the user with a list of customized tasks.
The Activities section provides a configured set of links that guide the user to certain tasks. The text of the activity and how the activity appears can be based on specific conditions, such as the presence of discrepancies.
This chapter includes the following topics:
Section 10.5, "Activity Tags and Functions Included with Oracle Clinical"
Section 10.6, "Creating and Maintaining Customized Activities"
RDC automatically displays the Activities section on the left side of the Home page. You do not need to configure any setting to display the Activities list.
Table 10-1 lists the default activities included in RDC.
Table 10-1 Default Activities for RDC
Title of the Activity Link Displayed on the Home Page | Clicking the Activities link… | Condition for Display |
---|---|---|
Review n Active Discrepancies |
Opens the Review Discrepancies page and |
If there is at least one active discrepancy in the site with an active discrepancy for the current user |
Review n Other Discrepancies |
Opens the Review Discrepancies page and |
If there is at least one other discrepancy in the site with an other discrepancy for the current user |
Review non-blank CRFs ready for initial verification |
Opens the Review CRFs page and |
If there is at least one CRF entered for a site |
Review non-blank CRFs ready for initial approval |
Opens the Review CRFs page and |
If there is at least one CRF entered for a site |
Review Investigator comments |
Opens the Review Investigator Comments page and |
If there is at least one Investigator Comment entered for a patient |
This section describes the following concepts for activities:
You can define an activity for a user role, a set of user roles, or all roles. If you specify one or more roles, the activity is applicable only to users with one of the specified roles. For example, if an activity list is set up for the DM role, the activity list defined would come up for all users to whom the DM role is granted.
Each activity is a pre-defined shortcut that RDC displays as a link. There are two types of activities:
Procedure — A procedure activity executes the defined query, navigates to the appropriate RDC page, and displays only those patients or CRFs that meet your defined search criteria. You use this type of activity for procedure-based tasks.
URL — A URL activity launches a browser instance, opens a new browser window, and automatically navigates to the Web page associated with the URL you specified. You use this type of activity for URL-based tasks.
For RDC, the activity scope determines the page navigation. You can define an activity to navigate to the Review CRFs page, the Review Discrepancies page, the Review Investigator Comments page, or a Web page.
Additionally, RDC always processes a procedure activity for the current site. Therefore, the scope does not define whether the activity is applicable to a specific study, site, or patient.
To create and maintain activities list, you use the following database tables:
The RDC_ACTIVITIES database table, which contains the setup information for all the activities defined for the RDC application
The RDC_ACTIVITIES_TAGS database table, which contains the tags that call the functions that provide values for a text string, a display expression, or a URL
The sections that follow provide more information about each table.
The Oracle Clinical RDC_ACTIVITIES database table contains the setup information for all the activities defined for the RDC application. You can add new activities, modify existing activities, and delete activities.
Each record (that is, row) in the RDC_ACTIVITIES database table defines the setup information for one activity. Note that the database table includes the default activities listed in Table 10-2.
Each field (that is, column) defines the values for an activity. Table 10-2 describes each column in the RDC_ACTIVITIES database table.
Table 10-2 RDC_ACTIVITIES Database Table
Column Name | Description | Type (Max Length) |
---|---|---|
|
Specifies for which user roles RDC displays the activity. Each user logging in to RDC is associated with only one role. You can use the user role to control which activities RDC displays when the user logs in to the application. You can specify:
For example, assume you define the following activities and user roles:
For this example, RDC displays:
The user role is not the only value that controls when RDC displays an activity. The value defined in the |
VARCHAR2 (50) |
|
Specifies the title of the activity. This is the title the user sees in the RDC application. Examples of title text:
You must specify the The When you specify a tag in the
For example, assume the TEXT column has the following line:
For this example, the resulting title in the RDC application might be:
You can also customize the return value of the function to take into account the applicable site. |
VARCHAR2 (200) |
|
Provides a unique key for the activity. |
Number (10) |
|
Defines the type of activity.
|
VARCHAR2 (15) |
|
Defines the scope in which the activity is eligible for display. The possible values for For RDC (4.5.3 and later),
|
VARCHAR2 (15) |
|
Defines what task RDC executes when the user clicks the activity link. The value you specify depends on the type of activity you are creating. If the
If the U = https://company.com/v_rsdir/study%CURR_STUDY%.htm U = https://opa.apps.company.com/v_rsdir/rdcservlet?study=%CURR_STUDY%&site=%CURR_SITE% For these URL examples, you must create the study specific Web pages or a servlet that accepts study and site as parameters. See Section 10.6, "Creating and Maintaining Customized Activities" for more information. |
VARCHAR2 (200) |
|
Defines the condition that determines whether RDC displays the activity link. You can enter no value (null) or you can specify a conditional display expression associated with the activity. The expression can include tags. The expression must return a true or false value. Based on the value in the column, RDC determines whether to display the activity.
You use the %BLANK_COUNT%>0 %OPEN_DISCREPS%>0 %ZF_OTHER_DISC%>0 %ZF_OPEN_DISC%>0 |
VARCHAR2 (200) |
The RDC_ACTIVITY_TAGS database table contains the tags that are defined in the system and the execution module (function name) that evaluates the tag.
Table 10-3 describes the column in the RDC_ACTIVITY_TAGS table.
Table 10-3 RDC_ACTIVITIES_TAGS Database Table
Column Name | Description | Type (Max Length) |
---|---|---|
|
Defines the tag that you can then specify in the following columns in the RDC_ACTIVITIES database table:
When RDC finds a tag in a column, RDC then searches through the values in the ACTIVITY_TAG column looking for a match for the tag.
When each of these columns is parsed, if a TAG is found, the process searches this column to find the TAG match so that it can identify the code or substitution variable to display in place of the TAG. If a match is found, it processes the expression found in the |
VARCHAR2 (15) |
|
Defines the type of function specified in the FUNCTION_NAME column.
|
VARCHAR2 (15) |
|
Defines either the name of the substitution variable or the name of the function in the system. Depending on the FUNCTION_TYPE value, the value in this column can be one of several types.
|
VARCHAR2 (61) |
Table 10-4 lists the default activity tags and the corresponding functions included with Oracle Clinical and configured to work with existing functions.
You can add records to the RDC_ACTIVITY_TAGS database table to point to a function you create or to a pre-existing function.
Table 10-4 Default Activity Tags and Corresponding Functions
Activity Tag | Function Name | Description | Valid For |
---|---|---|---|
|
RDC_ZF_surround_discrep.getOpenDiscrep |
Count of active discrepancies for the current context |
RDC only |
|
RDC_ZF_surround_discrep.getOtherDiscrep |
Count of other discrepancies for the current context |
RDC only |
|
Rdc_user_activity_tags.getCurrRole |
Role of the current user |
All versions of RDC |
|
Rdc_user_activity_tags.getCurrSite |
Site in context |
All versions of RDC |
|
Rdc_user_activity_tags.getCurrStudy |
Study in context |
All versions of RDC |
As described in Table 10-1, RDC has a set of default activities.
By modifying the RDC_ACTIVITIES database table, which contains the setup information for all the activities defined for the RDC application, you can:
Create and add new activities
Modify an existing activity
Copy an existing activity and then modify it to create a new activity
Delete an activity
In addition, you can create an activity procedure.
In SQL*Plus, use SELECT * FROM rdc_activities
to find the values for columns in an existing table.
To use these values to create procedural or link activities:
Log in to the database server.
Execute opa_setup:
opa_setup
database-name
Connect to SQL*Plus by entering:
sqlplus rxc/
rxcpassword
Execute the below query to determine the maximum activity ID that exists in the database:
SELECT max (activity_id) FROM rdc_activities;
If this is a task-based (procedural) activity, use an existing activity procedure or create a new one as instructed in Section 10.6.2. If this is a link activity, set up the Web service referred to by the link, if necessary.
Create an insert script like the example below, copying the text, activity_scope, activity_type, activity_exec, and display_expr from the original activity that you want to replicate.
For example, enter:
INSERT INTO rdc_activities (ACTIVITY_ID, CREATION_TS, CREATED_BY, USER_ROLE, TEXT, ACTIVITY_SCOPE, ACTIVITY_TYPE, ACTIVITY_EXEC, DISPLAY_EXPR) VALUES (&activityid, sysdate, 'RXC', 'DM,CRA&userrole', 'Review %OTHER_DISCREPS% Other Discrepancies', 'PATIENT', 'P', 'rdc_activity_std.process_other_discreps', '%OTHER_DISCREPS%>0'); COMMIT;
When prompted for the activity ID, enter a value just greater than the maximum activity ID in the database or greater than 20,000. This is to avoid conflicts with the default activities shipped with Oracle Clinical.
When prompted for the user role, enter the role for which you want to create this activity.
To copy an activity, you can use values from an existing activity to populate a new activity record and then modify the columns as appropriate.
For example, enter the following command in SQL*Plus, replacing <ID> with the ID of the activity you want to copy:
INSERT INTO rdc_activities (ACTIVITY_ID, CREATION_TS, CREATED_BY, USER_ROLE, TEXT, ACTIVITY_SCOPE, ACTIVITY_TYPE, ACTIVITY_EXEC, DISPLAY_EXPR) VALUES (&activityid, sysdate, CREATED_BY, USER_ROLE, TEXT, 'RDC_ZF_DISCREP', ACTIVITY_TYPE, ACTIVITY_EXEC, DISPLAY_EXPR) WHERE activity_id = <ID>; COMMIT;
To delete an activity, enter the following command, replacing <ID> with the ID of the activity that you want to delete:
DELETE FROM rdc_activities WHERE activity_id =
<ID>;
Alternatively, you can update the user role to NONE, preventing the activity from being used. To do so, use the following command, replacing <ID> with the ID of the activity that you want to take out of use:
UPDATE rdc_activities SET user_role = 'NONE' WHERE activity_id =
<ID>;
The activity_exec in the ACTIVITY_TAGS table specifies the activity procedure that gets executed immediately before a procedure-based activity (versus URL) is executed. The activity procedure is used to load query parameters before executing the query for a page.
To specify each search parameter and its value, use the following procedure:
rdc_zf_surround_activity.setSearchCondition (parameter,value)
For example, the following procedure retrieves only CRFs with a book ID of 123:
rdc_surround.setSearchCondition (BOOK_ID, 123)
Table 10-5 lists the parameters that you can specify in the search procedure. For examples, see the existing procedure specified in the ACTIVITY_EXEC column of rdc_activities where the scope is like %ZF%
.
When creating a new activity procedure, create your own package instead of using the packages included with Oracle Clinical. See Doc ID 2151942.1 on My Oracle Support for details on use and examples.
Table 10-5 Parameters for the rdc_zf_surround_activitiy.setSearchCondition Procedure
Parameter | Description | Example/Possible Values |
---|---|---|
|
Study ID |
24601 |
|
Site ID |
4501 |
|
DCI book ID |
1000 |
|
Starting range value for the patient ID |
F101 |
|
Ending range value for the patient ID |
F106 |
|
Visit ID |
10001 |
|
DCI ID |
101 |
|
Approval status |
NOT APPROVED/APPROVED |
|
Verification status |
NOT VERIFIED/VERIFIED |
|
Patient book ID |
101 |
|
CRF entry status |
BLANK RECEIVED BATCH LOADED PASS 1 COMPLETE PASS 1 STARTED PASS 2 COMPLETE PASS 2 STARTED PASS 1 OR 2 COMPLETE |
|
Patient discrepancy status |
OTHER, ACTIVE |
|
CRF discrepancy status |
OTHER, ACTIVE |
|
Discrepancy status |
OTHER, ACTIVE |
|
Patient entry status |
USED, NOT_USED |
|
List of patient position IDs. Populated when you select a set of patients on the Home page or the Casebooks page, and then drill down. Can hold up to 360 IDs. |
100100, 100101 |
|
Use these parameters in addition to PATIENT_LIST if you have more than 360 IDs. With these parameters, you can store an additional 360 IDs per parameter. |
100100, 100101 |
|
List of received DCI IDs, up to 360 IDs. |
450001, 4500013, 4500014 |
|
Use these parameters in addition to CRF_LIST if you have more than 360 IDs. With these parameters, you can store an additional 360 IDs per parameter. |
100100, 100101 |
|
The name of the standard or custom review type. |
DM_REVIEW |
|
The current review status of the CRF to be acted upon. |
RQ |
The following default activities do not include counts:
Review CRFs ready for approval
Review CRFs ready for verification
Review Investigator Comments
You can, however, modify these activities to include counts. You can use existing activity_tags CRF_READY_VERI
, CRF_READY_APP
, or INV_COMMENTS
.
To use these, update the applicable activity tags so the text includes %tag_name
%. The following statements update:
Connect as RXC user SQL*Plus> UPDATE rdc_activities SET title = 'Review %CRF_READY_VERI% non-blank CRFs ready for initial verification' WHERE activity_id = 9528; SQL*Plus> UPDATE rdc_activities SET title = 'Review %CRF_READY_APP% non-blank CRFs ready for initial approval' WHERE activity_id = 9529; SQL*Plus> UPDATE rdc_activities SET title = 'Review %INV_COMMENTS% Investigator comments' WHERE activity_id = 9530; SQL*Plus> COMMIT;
Note:
The Activity tags used in the above queries are existing tags referring to the function that limits to max 1000 rows, update the activity tag function or create a new activity tag referring to a new custom function, include the activity tag in the above query.If you do not want to limit the count to 1,000, you will need to create your own function and create activity tag records to refer to these functions. The code for the function should be as follows:
FUNCTION <name> RETURN NUMBER IS nCount NUMBER(10); nClinicalStudyId NUMBER(10); nSiteId NUMBER(10); BEGIN nClinicalStudyId := rdc_daapi.getStudyId; nSiteId := rdc_daapi.getSiteId; <select statement from table below>; RETURN ncount; END;
The SQL statements in Table 10-6 will calculate the counts.
Table 10-6 SQL Statements to Calculate Counts
Count of… | SQL statement |
---|---|
CRFs Ready for Approval |
SELECT /*+ index(rd RECEIVED_DCI_SITE_NFK_IDX) */ count(1) INTO nCount FROM received_dcis rd, patient_positions pp1 WHERE rd.patient_position_id = pp1.patient_position_id AND pp1.has_data_flag = 'Y' AND pp1.clinical_study_id = nClinicalStudyId AND rd.end_ts = to_date(3000000,'J') AND rd.clinical_study_id = nClinicalStudyId AND rd.site_id = nSiteId AND rdc.rdci_verify_status(RDC_ZF_surround.fetchMode,RD.received_dci_id) = 'VERIFIED' AND rdc.rdci_verify_status(RDC_ZF_surround.fetchMode,RD.received_dci_id) = 'VERIFIED' AND rdc_daapi.hasDiscrep(rd.received_dci_id,'NONE')='Y' AND decode(RD.received_dci_status_code,'PASS 1 COMPLETE',1,'PASS 2 STARTED',1,'PASS 2 PENDING',1,'PASS 2 COMPLETE',1,0) = 1 |
CRFs Ready for Verification |
SELECT /*+ index(rd RECEIVED_DCI_SITE_NFK_IDX) */ count(1) INTO nCount FROM received_dcis rd, patient_positions pp1 WHERE rd.patient_position_id = pp1.patient_position_id AND pp1.has_data_flag = 'Y' AND pp1.clinical_study_id = nClinicalStudyId AND rd.end_ts = to_date(3000000,'J') AND rd.clinical_study_id = nClincalStudyId AND rd.site_id = nSiteId AND decode(rdc.rdci_verify_status(RDC_ZF_surround.fetchMode,RD.received_dci_id),'NOT VERIFIED',1,'UNDONE',1,0) = 1 AND rdc_daapi.hasDiscrep(rd.received_dci_id,'NONE')='Y' AND decode(RD.received_dci_status_code,'PASS 1 COMPLETE',1,'PASS 2 STARTED',1,'PASS 2 PENDING',1,'PASS 2 COMPLETE',1,0) = 1 |
Investigator Comments |
SELECT /*+ index(pp1 PATIENT_POSITION_PK_IDX) index(rd RECEIVED_DCI_PK_IDX) index(respX RESPONSE_PK_IDX) index(rdcmX RECEIVED_DCM_PK_IDX)*/ count(1) INTO nCount FROM responses respX,received_dcms rdcmX,received_dcis rd,patient_positions pp1 WHERE dcmX.RECEIVED_DCM_ID = respX.RECEIVED_DCM_ID AND respX.clinical_study_id = nClinicalStudyId AND rdcmX.end_ts = to_date(3000000,'J') AND rd.end_ts = to_date(3000000,'J') AND rd.received_dci_id = rdcmX.received_dci_id AND pp1.patient_position_id = rd.patient_position_id AND respX.end_ts = to_date(3000000,'J') AND pp1.clinical_study_id = nClinicalStudyId AND rd.site_id = nSiteId AND respX.data_comment_text is not null |
If the existing activity tags do not provide what you are looking for, you can create a new activity tag and corresponding activity tag functions. You should create your own package and not use those packages included with Oracle Clinical.
Activity tag functions commonly execute a SQL statement to count the applicable activity.
If the activity count causes performance problems, there are various ways to improve performance if tuning the specific SQL statement does not work. You can limit the count to first 1,001 rows, and return >1,000 if more than 1,000. If two activities require processing a similar set of records, you could calculate two counts simultaneously, return the first count, and buffer the second count. The function for the second activity could return the buffered value. This technique requires that the activities are displayed in a specific order.
Table 10-7 lists the functions included in the RDC_USER_ACTIVITY_TAGS
package that you can use in your activity tag function.
Table 10-7 Functions Included in the RDC_USER_ACTIVITY_TAGS Package
Functions | Description |
---|---|
|
Populates the activity with patients/CRFs having the type of discrepancy passed as parameter: ACTIVE/OTHER. |
|
Force refreshes the results returned by the activity. |
|
Returns the next available unused patient position in the study. |
|
Populates the activity with patients/CRFs having ACTIVE discrepancies. |
|
Populates activity with patients/CRFs having OTHER discrepancies. |
|
Returns all if the current patient in context has data and new if the patient doesn't have data entered. |
|
Returns blank if the current patient in context has no data, red patient if the patient has active discrepancies, yellow patient if the patient has other discrepancies, and white patient if the patient data has no discrepancies. |
|
Adds the current site and patient in context to the activity query built. |
|
Adds the current site (or the passed site) to the activity query built. |
|
Resets the site to ALL, to process the activities query for all the sites in the study. |
|
Sets the site passed to the activities search context, fetches the next available patient, and sets the next available patient also in context. |
|
Assigns the site passed (if any) to the activity search context and sets the activity to search for ACTIVE discrepancies. Also, sets the current patient (if set) to the search context. |
|
Assigns the site passed (if any) to the activity search context and sets the activity to search for OTHER discrepancies. Also, sets the current patient (if set) to the search context. |
|
Returns flag that indicates if patients are included in the activity search. |
|
Sets flag that indicates if patients are to included in the activity search to TRUE to improve the performance of the activity queries. |
|
Sets flag that indicates if patients are to included in the activity search to FALSE. Also, to improve the performance of the activity queries. |