1. Administrator's Guide
  2. Customizing Activities
  3. Creating and Maintaining Customized Activities

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:

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:

  1. Log in to the database server.
  2. Execute opa_setup:

    opa_setup database-name

  3. Connect to SQL*Plus by entering:

    sqlplus rxc/rxcpassword

  4. Execute the below query to determine the maximum activity ID that exists in the database:

    SELECT max (activity_id) FROM rdc_activities;

  5. 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.
  6. 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;
  7. 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.
  8. When prompted for the user role, enter the role for which you want to create this activity.

For more information, see:

Parent topic: Creating and Maintaining Customized Activities

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)

creating-and-maintaining-customized-activities.html#GUID-1F8C42D5-83F8-4CBD-AFCC-C6EC7681ACCC__CIHBFFHC 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

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

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