Creating and Maintaining Customized Activities
As described in Default Activities for RDC, 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
Besides, you can create an activity procedure.
For more information, see:
- Working with the RDC_ACTIVITIES Table
- Creating an Activity Procedure
- Adding Counts to Existing Activities
Parent topic: Customizing Activities
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 Creating an Activity 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.
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.
For more information, see:
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;
Parent topic: Working with the RDC_ACTIVITIES Table
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>;
Parent topic: Working with the RDC_ACTIVITIES Table
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_zf_surround_activity.setSearchCondition (BOOK_ID, 123)
Table 10-3 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-3 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 |
Parent topic: Creating and Maintaining Customized Activities
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 text = 'Review %CRF_READY_VERI% non-blank CRFs
ready for initial verification' WHERE activity_id = 9528;
SQL*Plus>
UPDATE rdc_activities SET text = 'Review %CRF_READY_APP% non-blank CRFs
ready for initial approval' WHERE activity_id = 9529;
SQL*Plus>
UPDATE rdc_activities SET text = '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 the following table calculate the 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 |
Parent topic: Creating and Maintaining Customized Activities