10 Customizing Activities

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.1, "Displaying the Activities List"
Section 10.2, "Default Activities for RDC"
Section 10.3, "Key Concepts for Activities"
Section 10.4, "Activities Database Tables"
Section 10.5, "Activity Tags and Functions Included with Oracle Clinical"
Section 10.6, "Creating and Maintaining Customized Activities"
Section 10.7, "Creating an Activity Tag Function"

10.1 Displaying the Activities List

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.

Description of the illustration ''activities02.gif''

10.2 Default Activities for RDC

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

10.3 Key Concepts for Activities

This section describes the following concepts for activities:

User Roles and Activities
Types of Activities
Scope of Activities

10.3.1 User Roles and 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.

10.3.2 Types of Activities

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.

10.3.3 Scope of Activities

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.

10.4 Activities Database Tables

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.

10.4.1 RDC_ACTIVITIES Database 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)
`USER_ROLE`	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: One role Multiple roles (use a comma to separate roles) All roles (use the % wildcard character) For example, assume you define the following activities and user roles: Activity 1: `USER_ROLE` = % Activity 2: `USER_ROLE` = DM Activity 3: `USER_ROLE` = DM, CRA, SITE For this example, RDC displays: All three activities when the data manager (DM) logs in Only Activity 1 and Activity 3 when the CRA logs in Only Activity 1 when the investigator (INV) logs in The user role is not the only value that controls when RDC displays an activity. The value defined in the `DISPLAY_EXPR` column defines the condition that must be meet for RDC to display the activity. See the `DISPLAY_EXPR` description later in this table for more information.	VARCHAR2 (50)
`TEXT`	Specifies the title of the activity. This is the title the user sees in the RDC application. Examples of title text: Show All My Open Discrepancies Review All Investigator Comments Go to the Oracle Web Site You must specify the `TEXT` value in English. RDC currently only supports English. The `TEXT` value can contain one or more tags. A tag provides variable information, such as the site name, a count of records, or a refer function. You use % signs to enclose a tag. For example, `%OPEN_DISCREPS%`. When you specify a tag in the `TEXT` column, RDC processes the tag as follows: Locates the tag in the `RDC_ACTIVITY_TAGS` table Calls the function specified in the `FUNCTION_NAME` column in that table Replaces the tag with the value returned by the called function For example, assume the TEXT column has the following line: `Show` `%OPEN_DISCREPS%` `Active Discrepancies for Site` `%CURR_SITE%` For this example, the resulting title in the RDC application might be: `Show 3 Active Discrepancies for Site ACME DRUG CO` You can also customize the return value of the function to take into account the applicable site.	VARCHAR2 (200)
`ACTIVITY_ID`	Provides a unique key for the activity.	Number (10)
`ACTIVITY_TYPE`	Defines the type of activity. P — Indicates that this is a procedure activity. Recall that a procedure activity executes the defined query, navigates to the appropriate RDC page, and displays only those patients/CRFs that meet your defined search criteria. You use this type of activity for procedure-based tasks. U — Indicates that this is a URL activity. Recall that a URL activity launches a browser instance, opens a new browser window, and automatically navigates to the Web page associated with the URL. You use this type of activity for URL-based tasks.	VARCHAR2 (15)
`ACTIVITY_SCOPE`	Defines the scope in which the activity is eligible for display. The possible values for `ACTIVITY_SCOPE` depend on your version of the RDC application. For RDC (4.5.3 and later), `ACTIVITY_SCOPE` determines the page navigation. Because all activities in RDC are always processed for the current site, you do not need to define whether the activity is applicable to a specific study, site, or patient. RDC_ZF_CRF — Navigates to the Review CRFs page. RDC_ZF_DISCREP — Navigates to the Review Discrepancies page. RDC_ZF_INV — Navigates to the Review Investigator Comments page. RDC_ZF_LINK — Navigates to the Web page defined by the URL.	VARCHAR2 (15)
`ACTIVITY_EXEC`	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 `ACTIVITY_TYPE` is P, then the value in the ACTIVITY_EXEC column: Is the name of the procedure to call when the activity is executed. Procedures use standard variables to determine the current study, site, or patients. Therefore, tags are not necessary with procedures. Examples of procedures: `rdc_activity_std.process_open_discreps` `RDC_ZF_surround_activity.LoadInvComments('All')` If the `ACTIVITY_TYPE` is U, then the value in the ACTIVITY_EXEC column is the URL. As shown in the following examples, you can use tags to make the URL sensitive to the context: 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)
`DISPLAY_EXPR`	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. If the value is null, RDC always displays the activity. If the value of the specified expression evaluates to true, RDC displays the activity. If the value of the specified expression evaluates to false, RDC does not display the activity. You use the `DISPLAY_EXPR` column to suppress activities if no records match a specific condition. For example, you can define that RDC display a link to view all active discrepancies only if the number of open discrepancies is greater than zero (`%OPEN_DISCREPS%` > `0`). %BLANK_COUNT%>0 %OPEN_DISCREPS%>0 %ZF_OTHER_DISC%>0 %ZF_OPEN_DISC%>0	VARCHAR2 (200)

10.4.2 RDC_ACTIVITY_TAGS Database Table

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)

Column Name	Description	Type (Max Length)
`ACTIVITY_TAG`	Defines the tag that you can then specify in the following columns in the RDC_ACTIVITIES database table: TEXT ACTIVITY_EXEC DISPLAY_EXPR 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. Locates the tag in the `RDC_ACTIVITY_TAGS` table Calls the function specified in the `FUNCTION_NAME` column in that table Replaces the tag with the value returned by the called function 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 `FUNCTION_NAME` column.	VARCHAR2 (15)
`FUNCTION_TYPE`	Defines the type of function specified in the FUNCTION_NAME column. F — Indicates the function is a user-defined function. S — Indicates the function contains search parameters. You use this type as a place holder for the search parameters (if they need to be brought up as part of the URL link). P — Indicates the function contains one of the following parameters: SITE, STUDY, or PATIENT. RDC sets the tag to the name to apply to the site, study, or patient. You cannot define new tags as parameter tags. The valid values are restricted to the name of the site, study, or patient.	VARCHAR2 (15)
`FUNCTION_NAME`	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. If the FUNCTION_TYPE is F, then this column holds a database function, for example rdc_activity_std.open_discreps. See Function Activity Tags for examples of activity tags with a function type of F. If the FUNCTION_TYPE is S, then, this column holds a database function, e. g. RDC_ZF_surround_crf.getCrfsCountReadyForVerify passing required values for the function execution internally. If the FUNCTION_TYPE is P, then the only valid value is SITE. The tag gets the value of the current site being processed. If the FUNCTION_TYPE is T, then this value refers to a trigger.	VARCHAR2 (61)

ACTIVITY_TAG

Defines the tag that you can then specify in the following columns in the RDC_ACTIVITIES database table:

TEXT
ACTIVITY_EXEC
DISPLAY_EXPR

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.

Locates the tag in the RDC_ACTIVITY_TAGS table
Calls the function specified in the FUNCTION_NAME column in that table
Replaces the tag with the value returned by the called function

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 FUNCTION_NAME column.

VARCHAR2 (15)

FUNCTION_TYPE

Defines the type of function specified in the FUNCTION_NAME column.

F — Indicates the function is a user-defined function.
S — Indicates the function contains search parameters. You use this type as a place holder for the search parameters (if they need to be brought up as part of the URL link).
P — Indicates the function contains one of the following parameters: SITE, STUDY, or PATIENT. RDC sets the tag to the name to apply to the site, study, or patient. You cannot define new tags as parameter tags. The valid values are restricted to the name of the site, study, or patient.

VARCHAR2 (15)

FUNCTION_NAME

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.

If the FUNCTION_TYPE is F, then this column holds a database function, for example rdc_activity_std.open_discreps. See Function Activity Tags for examples of activity tags with a function type of F.
If the FUNCTION_TYPE is S, then, this column holds a database function, e. g. RDC_ZF_surround_crf.getCrfsCountReadyForVerify passing required values for the function execution internally.
If the FUNCTION_TYPE is P, then the only valid value is SITE. The tag gets the value of the current site being processed.
If the FUNCTION_TYPE is T, then this value refers to a trigger.

VARCHAR2 (61)

10.5 Activity Tags and Functions Included with Oracle Clinical

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
`ZF_OPEN_DISC`	RDC_ZF_surround_discrep.getOpenDiscrep	Count of active discrepancies for the current context	RDC only
`ZF_OTHER_DISC`	RDC_ZF_surround_discrep.getOtherDiscrep	Count of other discrepancies for the current context	RDC only
`CURR_ROLE`	Rdc_user_activity_tags.getCurrRole	Role of the current user	All versions of RDC
`CURR_SITE`	Rdc_user_activity_tags.getCurrSite	Site in context	All versions of RDC
`CURR_STUDY`	Rdc_user_activity_tags.getCurrStudy	Study in context	All versions of RDC

10.6 Creating and Maintaining Customized Activities

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.

10.6.1 Working with the RDC_ACTIVITIES Table

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.

10.6.1.1 Copying Activities

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;

10.6.1.2 Deleting Activities

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

10.6.2 Creating an Activity Procedure

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`	Study ID	24601
`SITE_ID`	Site ID	4501
`DCI_BOOK_ID`	DCI book ID	1000
`PATIENT_FROM`	Starting range value for the patient ID	F101
`PATIENT_TO`	Ending range value for the patient ID	F106
`VISIT_ID`	Visit ID	10001
`DCI_ID`	DCI ID	101
`APPROVAL_STATUS`	Approval status	NOT APPROVED/APPROVED
`VERIFICATION_STATUS`	Verification status	NOT VERIFIED/VERIFIED
`PATIENT_BOOK_ID`	Patient book ID	101
`CRF_ENTRY_STATUS`	CRF entry status	BLANK RECEIVED BATCH LOADED PASS 1 COMPLETE PASS 1 STARTED PASS 2 COMPLETE PASS 2 STARTED PASS 1 OR 2 COMPLETE
`PAT_DISC_STATUS`	Patient discrepancy status	OTHER, ACTIVE
`CRF_DISC_STATUS`	CRF discrepancy status	OTHER, ACTIVE
`DISC_STATUS`	Discrepancy status	OTHER, ACTIVE
`PATIENT_ENTRY_STATUS`	Patient entry status	USED, NOT_USED
`PATIENT_LIST`	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
`PATIENT_LIST1, PATIENT_LIST2, PATIENT_LIST3`	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
`CRF_LIST`	List of received DCI IDs, up to 360 IDs.	450001, 4500013, 4500014
`CRF_LIST1, CRF_LIST2, CRF_LIST3`	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
`REVIEW_TYPE`	The name of the standard or custom review type.	DM_REVIEW
`REVIEW_STATUS`	The current review status of the CRF to be acted upon.	RQ

10.6.3 Adding Counts to Existing Activities

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

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

10.7 Creating an Activity Tag Function

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
`ANY_DISCREPS`	Populates the activity with patients/CRFs having the type of discrepancy passed as parameter: ACTIVE/OTHER.
`ENFORCEREQUERY`	Force refreshes the results returned by the activity.
`NEXT_PATIENT`	Returns the next available unused patient position in the study.
`OPEN_DISCREPS`	Populates the activity with patients/CRFs having ACTIVE discrepancies.
`OTHER_DISCREPS`	Populates activity with patients/CRFs having OTHER discrepancies.
`PATIENT_DATA`	Returns all if the current patient in context has data and new if the patient doesn't have data entered.
`PAT_STATUS_ICON`	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.
`PROCESS_CURR_PATIENT`	Adds the current site and patient in context to the activity query built.
`PROCESS_CURR_SITE`	Adds the current site (or the passed site) to the activity query built.
`PROCESS_CURR_STUDY`	Resets the site to ALL, to process the activities query for all the sites in the study.
`PROCESS_NEXT_PATIENT`	Sets the site passed to the activities search context, fetches the next available patient, and sets the next available patient also in context.
`PROCESS_OPEN_DISCREPS`	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.
`PROCESS_OTHER_DISCREPS`	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.
`FILTERINCLUDESPATIENT`	Returns flag that indicates if patients are included in the activity search.
`RESETINCLUDESPATIENT`	Sets flag that indicates if patients are to included in the activity search to TRUE to improve the performance of the activity queries.
`SETINCLUDESPATIENT`	Sets flag that indicates if patients are to included in the activity search to FALSE. Also, to improve the performance of the activity queries.