9 Customizing Activities

In RDC Onsite, 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:

Displaying the Activities List
Default Activities for RDC Onsite
Key Concepts for Activities
Activities Database Tables
Activity Tags and Functions Included with Oracle Clinical
Creating and Maintaining Customized Activities
Creating an Activity Tag Function

9.1 Displaying the Activities List

RDC Onsite 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

9.2 Default Activities for RDC Onsite

Table 9-1 lists the default activities included in RDC Onsite.

Table 9-1 Default Activities for RDC Onsite

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

9.3 Key Concepts for Activities

This section describes the following concepts for activities:

User Roles and Activities
Types of Activities
Scope of Activities

9.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 DM role, the activity list defined would come up for all users to whom the DM role is granted.

9.3.2 Types of Activities

Each activity is a pre-defined shortcut that RDC Onsite 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/CRFs that meet your defined search criteria. You use this type of activity for procedure-based tasks.

For RDC Classic, a procedure activity can also be a trigger activity that executes a specific Oracle Clinical function. Trigger activities apply to RDC Classic only.
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.

9.3.3 Scope of Activities

The meaning of activity scope varies depending on your version of the RDC application:

For RDC Onsite, 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.

In addition, RDC Onsite 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.

For RDC Classic, the activity scope determines where to display and how to restrict the activity. The scope also defines whether the activity is applicable to the study, site, or patient.

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

9.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 9-2.

Each field (that is, column) defines the values for an activity. Table 9-2 describes each column in the RDC_ACTIVITIES database table.

Table 9-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 Onsite HTML currently only supports English. For RDC Classic, you can use the TEXT_NLS column to specify the title in Japanese for the NLS version of RDC Classic. 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. For RDC Classic, a procedure activity can also be a trigger activity that executes a specific Oracle Clinical function. Trigger activities apply to RDC Classic only. 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 Onsite HTML (4.5.3 and later), `ACTIVITY_SCOPE` determines the page navigation. Because all activities in RDC Onsite HTML 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. For RDC Onsite PDF (4.5.2): ONSITE_CRF — Navigates to the Review CRFs screen. ONSITE_DISCREP — Navigates to the Review Discrepancies screen. ONSITE_LINK — Navigates to the Web page defined by the URL. For RDC Classic, `ACTIVITY_SCOPE` determines when to display and how to restrict the activity. You can define whether the activity is applicable to the study, site, or patient. STUDY — Displays when you initially open the activity screen or when you click on the study in the navigator panel. XSITE — Displays once for each site when you initially open the activity screen or when you click on the study in the navigator panel. For example, if four sites match the specified condition, then RDC Classic displays the activity four times. SITE — Displays when you click on the site name in the navigator panel if the site matches the specified criteria. PATIENT — Displays when you click on the patient name in the navigator panel if the patient matches the specified criteria.	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')` Is a trigger activity to execute specific Oracle Clinical functions. Trigger activities apply to RDC Classic only. 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://oracle.com/v_rsdir/study%CURR_STUDY%.htm` `U = https://opa.apps.us.oracle.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 9.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)
`TEXT_NLS`ACTIVE_NLS STATUS_NLS LANGUAGE	Provide support for the Japanese language for the RDC Classic NLS product. These columns do not apply to RDC Onsite, which currently only supports English.	RDC Classic only

9.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 9-3 describes the column in the RDC_ACTIVITY_TAGS table.

Table 9-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 Onsite 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. T — Indicates the function is a trigger activity. Trigger activities apply to RDC Classic only.	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 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 Onsite 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.
T — Indicates the function is a trigger activity. Trigger activities apply to RDC Classic only.

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

9.5 Activity Tags and Functions Included with Oracle Clinical

Table 9-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 9-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 Onsite only
`ZF_OTHER_DISC`	RDC_ZF_surround_discrep.getOtherDiscrep	Count of other discrepancies for the current context	RDC Onsite 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

9.6 Creating and Maintaining Customized Activities

As described in Table 9-1, RDC Onsite 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.

9.6.1 Working with the RDC_ACTIVITIES Table

From SQL, 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 the command line:

sqlplus rxc/rxcpassword
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, create a new activity procedure, unless using you are using an existing procedure. 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. When prompted for 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 activities included by Oracle Clinical. When prompted for the user role, enter the role for which you want to create this activity.

For example, enter the following command in a SQL session:

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;

9.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 a SQL session:

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 = 72; COMMIT;

If copying from one surround to another (for instance, RDC Classic to RDC Onsite HTML), create a new activity procedure and specify the value in ACTIVITY_EXEC.

9.6.1.2 Deleting Activities

To delete an activity, enter the following command:

DELETE FROM rdc_activities WHERE activity_id = 31;

Alternatively, you can update the user role to NONE:

DATE rdc_activities SET user_role = 'NONE' WHERE activity_id = 31;

9.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 sets the BOOK_ID parameter to retrieve only CRFs with a book ID of 123:

rdc_surround.setSearchCondition (BOOK_ID, 123)

Table 9-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, you should create your own package and not use those packages included with Oracle Clinical.

Table 9-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.	100100, 100101
`CRF_LIST`	List of received DCI IDs	450001, 4500013, 4500014

9.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 9-6 will calculate the counts.

Table 9-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

9.7 Creating an Activity Tag Function

The FUNCTION_NAME column in ACTIVITY_TAGS table specifies the function that gets executed when a tag is evaluated. You can look at the existing functions specified in FUNCTION_NAME column of rdc_activities to see examples.

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 9-7 lists the functions included in the RDC_ACTIVITY_STD package that you can use in your activity tag function.

Table 9-7 Functions Included in the RDC_ACTIVITY_STD Package

Functions	Description
`GetCurrStudy`	Returns the study in current context. No parameters.
`GetCurrSite`	Returns the site in current context. No parameters.
`GetCurrScope`	Returns the scope. For example, `RDC_ZF_CRF`, `RDC_ZF_DISCREP`, `RDC_ZF_INV`, `RDC_ZF_LINK`
`GetTestProd`	Returns the mode for the user currently logged in to RDC Onsite. P for Production mode; T for Test mode.
`GetCurrRole`	Returns the role of user currently logged in to RDC Onsite. No parameters.