Oracle Clinical provides a set of utilities for performing tasks that are easier to accomplish from a command line or that cannot be done from the user interface.
Computing the Validation Status of All Responses covers cnvstatus
Generating Validation Procedures covers gen_procs
Deleting Inactive Procedures covers rxcdelproc
Running Reports on Deleted Data and Discrepancies covers:
list_asdd_responses
list_asdd_discrepancies
Other utilities are covered in other chapters:
"Generating Data Extract Views" covers gen_views
"Changing the Password for a Schema or Role Using the SET_PWD Utility" covers set_pwd
"Starting and Stopping PSUB" covers start_psub
"Starting and Stopping PSUB" covers stop_psub
Use the cnvstatus utility to compute a validation status for all responses. The utility populates a column in the RESPONSES table that contains the validation status of each stored response. Before populating the response field VALIDATION_STATUS, you might want to add Discrepancy Resolution subtypes to distinguish various types of resolutions. You do this by entering values in the Long Value field of the reference codelist DISCREPANCY RESOLU TYPE CODE. This is an installation codelist you access from within Oracle Clinical which maintains user-defined discrepancy statuses.
You must select the values from the following list: NULL, CONFIRMED, IRRESOLVABLE, SUPERSEDED, or NOT DISCREPANT. The last two values are used only for manual discrepancies; they indicate that the discrepancy applied to a previous version of the response, or that the discrepancy was never really a problem with the data, but just a comment.
When the process is complete, examine the log, $RXC_LOG/cnvstatus.log, for errors.
To run cnvstatus on UNIX:
Log on to the server as opapps and change the directory to $RXC_TOOLS.
Set environment variables for the database name and code environment; see "Setting Environment Variables on the Command Line."
Set the output directory:
C Shell command: setenv RXC_LOG usr_log_dir
Bourne Shell command:
RXC_LOG = usr_log_dir
export = code_env
Run the script. It prompts for the RXC username and its password.
cnvstatus study_name or ALL
For example:
% cnvstatus ALL | Study Name
Where "ALL" is all studies in the database and Study_Name is the name of one study.
To run cnvstatus on Windows:
Log on to the server as opapps.
Open a DOS window, change directory to %RXC_TOOLS, and set the server environment; see "Setting Environment Variables on the Command Line."
Set the output directory:
set rxc_log=user_log_folder
Run the command file. For example:
cnvstatus ALL | Study_Name
Where "ALL" is all studies in the database and Study_Name is the name of one study.
With the gen_procs utility you can convert existing Validation Procedures to 3.1-style, and regenerate them, on a per-study basis. Its use is not required, or necessarily recommended, for upgrades or new installations of Oracle Clinical.
This utility has the following syntax:
gen_procs { ALL | study_name } { FULL | INC } { CONVERT | GENERATE | PARSE } { 31 | 30 | ALL }
Choose one option from each set of qualifiers:
ALL | study_name— specifies the study you want to apply to. Enter either an individual study name or ALL to include all studies. This qualifier is not case-sensitive.
FULL | INC — specifies whether to perform full or incremental replication. You select FULL when running from the command line. INC is used when replication runs this command. This qualifier is case-sensitive.
CONVERT | GENERATE | PARSE — specifies the action you want to take. CONVERT works only for pre-3.1 Procedures; it converts the Procedure to 3.1-style, as well as generating and parsing as part of processing. PARSE works only with 3.1-style Procedures; it parses and recreates the package. GENERATE works for 3.0, 3.1, or ALL procedures; it also parses each package. PARSE and GENERATE are used primarily when the utility is called for replication.
31 | 30 | ALL — specifies the version of the Procedures to process.
LAGONLY | ALL —see the secton on lag variables in the chapter on creating Procedures in Oracle Clinical Creating a Study
The system creates a file named ora_errors.err in one of the following locations:
If you specify a value for RXC_LOG, the system writes the error log to that directory.
If you do not specify a value for RXC_LOG, the system writes the error log to the user's subdirectory under the directory specified in the PSUB_LOGS_DIRECTORY value in the OCL_STATE local reference codelist.
To run gen_procs on a UNIX platform:
Log on to the server as opapps.
Set environment variables for the database name and code environment; see "Setting Environment Variables on the Command Line."
Change the directory to $RXC_BIN.
Set the output directory (Optional):
C Shell command: setenv RXC_LOG usr_log_dir
Bourne Shell command: RXC_LOG=usr_log_dir export code_env
If you do not specify a directory, the system places the files ora_errors.err and genprocs.log in the location specified as the value of PSUB_LOGS_DIR in the OCL_STATE local reference codelist.
Run the script. For example:
gen_procs ALL FULL GENERATE ALL > gen_procs.log
The system prompts for the database name and the username and password of any account that can execute a single procedure. You can use the RXC account.
Oracle Clinical creates gen_procs.log in $RXC_BIN.
To run gen_procs on Windows:
Log on to the server as opapps.
Set environment variables for the database name and code environment; see "Setting Environment Variables on the Command Line."
Set the output directory:
set RXC_LOG=user_log_folder
Run the command file. For example:
gen_procs ALL FULL GENERATE ALL > gen_procs.log
The system prompts for the database name and the username and password of any account that can execute a single procedure. You can use the RXC account.
Oracle Clinical creates genprocs.log in the current directory (%RXC_BIN%). Files ora_errors.err and genprocs.log are created in the RXC_LOG directory.
Oracle Clinical lets you delete unneeded Procedures from within the application. However, this does not actually delete the database packages that contain the Procedures, which may cause unwanted Procedures to accumulate. To delete them, go to the RXC_TOOLS directory, log in as RXC_PD, and execute the SQL script rxcdelproc.
You can run two reports in SQL*Plus to view data and discrepancies that were deleted using the batch job for that purpose if it was running in Audit mode:
List Batch Data Deleted Responses
List Batch Data Deleted Discrepancies
To run either report:
Log on to the operating system.
Set environment variables for the database name and code environment; see "Setting Environment Variables on the Command Line."
Change to the RXC_INSTALL directory:
cd $RXC_INSTALL
Connect to SQL*Plus as rxc:
sqlplus rxc
Run one of the scripts:
start list_asdd_responses.sql
or
start list_asdd_discrepancies.sql
The script prompts for the following parameter values:
Study: Enter either a single study or use % for wildcard.
Site: Enter either a single site or use % for wildcard.
Patient: Enter either a single patient name or use % for wildcard.
Document Number: Enter either a single document number or use % for wildcard.
User ID who submitted BDD job: Enter either a single user or use % for wildcard.
Batch Job ID: Enter a particular batch job id or return for all.
Job Submitted From: Enter the BDD job(s) executed from date.
Job Submitted To: Enter the BDD job(s) executed to date.
Spool file name: Enter the file name into which the job should write the output. You can include a relative path if needed.
Delimiter: Enter ~ or another character to simplify import into Excel.
The job writes data to the file specified as the spool file.
Note:
The list_asdd_discrepancies.sql script retrieves discrepancies that have been OBSOLETED because a response was corrected. There will be no corresponding record for the previous discrepant response. On the other hand, a multivariate discrepancy may be listed as CURRENT, even if a response has been updated to resolve that discrepancy. This happens if the discrepancy procedure is only executed when Batch Validation runs (an offline procedure), and Batch Data Delete is executed before Batch Validation has had a chance to OBSOLETE the discrepancy.