Customizing Data Extract Views

Oracle Clinical ships with two scripts in the INSTALL directory that you can use to customize data extract views.

rxcptdxvb.sql populates the data extract tables EXTRACT_KEYS and EXTRACT_MACROS and creates the standard key template.
pop_vb_static_views.sql creates the standard view templates, which include views for responses and RDCMs.

For example, follow these instructions to make the investigator's last name available to an extract macro:

Back up the scripts in the directory $RXC_INSTALL that are used to customize data extract views:
- pop_vb_static_views.sql
- rxcptdxvb.sql
Bring up pop_vb_static_views.sql in a text editor.

Make the changes marked in bold so that the updated code is as shown below:

… 
REM 
REM ORACLE TEXT FOR NEW STYLE RDCMS_VIEW 
REM 
DECLARE 
  LTEXT long; 
BEGIN 
/* SPR24128 JRees 9/24/98 Adding hints to security checks */ 
LTEXT := 
'create view \0.rdcms_view as 
select /*+ ORDERED USE_MERGE(css) 
           INDEX(rdcm RECEIVED_DCM_DCM_CS_NFK_IDX\4) 
           INDEX(css CLINICAL_STUDY_STATE_IDX) <- Remove '*/' from the end of this line.
/* to add additional tables, add index hint for joining  */ 
/* for example, for RDCIs add:                           */ 
/*         (note that \4 adds the T for test mode)       */ 
/*           INDEX(RDCI RECEIVED_DCI_PK_IDX\4)           */       
/* for example, for OCL_Investigators, add:              */ 
       INDEX(INV OCL_INVESTIGATOR\4_PK_IDX)   */ <- Remove '/*' from the start of this line.
       rdcm.received_dcm_id, 
       rdcm.dcm_id, 
       rdcm.dcm_subset_sn, 
       rdcm.dcm_layout_sn, 
       rdcm.actual_event_id, 
       rdcm.dci_id, 
       rdcm.received_dci_id, 
       rdcm.received_dcm_entry_ts, 
       rdcm.end_ts, 
       rdcm.entered_by, 
       rdcm.dcm_date, 
       rdcm.dcm_time, 
       rdcm.received_dcm_status_code, 
       rdcm.qualifying_value, 
       rdcm.accessible_ts, 
       rdcm.log_in_ts, 
       rdcm.last_data_change_ts, 
       rdcm.data_lock_flag, 
       rdcm.sn, 
       rdcm.document_number, 
       rdcm.modification_ts, 
       rdcm.modified_by, 
       rdcm.subevent_number, 
       rdcm.investigator_id, 
       rdcm.investigator, 
       inv.last_name,           <- Add this line. Remember the comma.
       rdcm.clin_plan_eve_id, 
       rdcm.clin_plan_eve_name, 
       rdcm.visit_number, 
       rdcm.site_id, 
       rdcm.site, 
       rdcm.lab_id, 
       rdcm.lab, 
       rdcm.lab_range_subset_num, 
       rdcm.LAB_ASSIGNMENT_TYPE_CODE, 
       rdcm.patient_position_id, 
       rdcm.patient, 
       rdcm.clinical_study_id 
/*  to add additional columns, add here,              */ 
/*  for example, for Received_DCIs, add:              */ 
/*       , RDCI.FIRST_BOOK_PAGE                       */ 
/*  for example, for OCL_Investigators, add:          */ 
/*       , INV.COUNTRY                                */ 
\5 
/* to customize, extend from list.                    */ 
/* for example, to add RDCIs add:                     */ 
/*   note: the \4 adds the T for test account         */ 
/*   , RECEIVED_DCIS\4 RDCI                           */ 
/* for example, to add OCL_Investigators add:         */ 
  , OCL_INVESTIGATORS\4 INV   <- Remove '/*' and '*/' from this line.
WHERE 
\1 
/* use \6 to access as_of_ts for account-specific     */ 
/* time restriction,                                  */ 
/* for example, to join RDCIS, add:                   */ 
/*  AND RDCM.RECEIVED_DCI_ID = RDCI.RECEIVED_DCI_ID   */     
/*  AND RDCI.END_TS > \6                              */ 
/*  AND RDCI.RECEIVED_DCI_ENTRY_TS <= \6              */ 
/* for example, to join OCL_Investigators, add:       */ 
AND RDCM.INVESTIGATOR_ID = INV.INVESTIGATOR_ID   <- Remove '/*' and '*/' from this line.
  and  (exists 
    /* account is super-user  */ 
          (select /*+ index(oa ORACLE_ACCOUNT_PK_IDX) */ 
…

Save the changes made to the pop_vb_static_views.sql file.
To add the column as an Extract Key, bring up rxcptdxvb.sql in a text editor.
Make the changes marked in bold:
```
delete from extract_keys 
where OC_INTERNAL_NAME in ( 
  '/STUDY', 
  'DCMS.SUBSET_NAME', 
  'DCMS.SUBSET_NAME', 
  'DCMS.DCM_SUBSET_SN', 
  'RDCM.DOCUMENT_NUMBER', 
  'RDCM.SITE', 
  'RDCM.INVESTIGATOR', 
  'RDCM.LAST_NAME',     <- Add this line. 
  'RDCM.PATIENT', 
  'RDCM.ACCESSIBLE_TS', 
  'RDCM.LOG_IN_TS', 
  'RDCM.LAST_DATA_CHANGE_TS', 
  'RDCM.DATA_LOCK_FLAG', 
  'RDCM.CLIN_PLAN_EVE_NAME', 
  'RDCM.DCM_DATE', 
  'RDCM.DCM_TIME', 
  'R.REPEAT_SN', 
  … 
```
Note:

The new key is added as RDCM.LAST_NAME and not INV.LAST_NAME. In this context RDCM is the alias for the RDCMS_VIEW view to which we added the new column by modifying pop_vb_static_view.sql.

The rxcptdxvb.sql file already contains the required modifications for adding FIRST_BOOK_PAGE from RECEIVED_DCIS and COUNTRY from OCL_INVESTIGATORS. No further modification of this file is therefore required for these columns if you have added them to pop_vb_static_view.sql.
Find the insert into extract_keys statement for RDCM.DCM_DATE and duplicate it to create an insert statement for the RDCI.INVESTIGATOR.
Save the changes made to the rxcptdxvb.sql file.
To populate the data extract tables with the customized definitions, log in to SQL*Plus as RXC and run both scripts:
```
SQL> start pop_vb_static_views.sql 
SQL> start rxcptdxvb.sql 
```
Note:

Note: If when running rxcptdxvb.sql you get ORA-0001: Unique constraint (RXC.EXTRACT_KEYS_ORA_UK_ID) violated then perform the following as RXC to clean up:
```
SQL> delete extract_keys;
SQL> commit;
```
Re-execute rxcptdxvb.sql and continue with the next step.
To confirm that the INVNAME extract key and macro are now available for use, Log into Oracle Clinical and navigate to Glib, then Data Extract View Builder, then Extract Macros and press F8 to query. INVNAME should now be included.
To add the extract key and extract macro for the new column to a Key Template, . Query for the template to which
1. Navigate to Glib, then Data Extract View Builder, then Key Templates.
2. Query for the template to which you want to add the macro.
3. Click on Key Columns.
4. In an empty field, press F9 to see the list of values.
5. Select INVNAME from the list.
6. Save.

The investigator's last name will appear as column INVNAME in all data extract views created with this Key Template.

Parent topic: Configuring Data Extract