2 Custom Identifier Handling

Overview

The OCL_CLIENT_PACK package consists of two files in the RXC_INSTALL directory, ocl_client_ps.sql and ocl_client_pb.sql. Refer to these files while reading this chapter. The files serve the following purposes:

• ocl_client_ps.sql

  • Package specification

  • Creating the public synonym

  • Granting execute privilege to both RXCLIN_MOD and RXCLIN_READ database roles

• ocl_client_pb.sql

  • Container for the function bodies for the OCL_CLIENT_PACK package

Use SQL*Plus from the RXC database account to execute ocl_client_ps.sql:

• when initially installing the Oracle Clinical application

• once after each time you modify the file

This file is a skeleton for creating custom validation and derivation because each function provides a stub; as delivered all validations succeeds and no derivation occurs. Each function includes detailed header comments that describe its purpose, parameters, and usage.

The functions in the OCL_CLIENT_PACK package are described in the following sections. The functions to customize for validation and derivation are ValidateName, ValidateDocument, and DeriveDocumentNumber. The functions to customize for page tracking are DefaultBookPageNumber, TrimPageNumber, ChangePageStatus, and AddPhysPageNumber. In each case, the function duplicates a function that already exists in the Oracle Clinical code. If you write new code for the function, your code supersedes the existing application code; otherwise, it simply uses the default system code. The SQL files ocl_client_ps.sql and ocl_client_pb.sql in the RXC_INSTALL are well commented, use them for additional information and to implement your custom functions.

ValidateName

This function is called by the validation triggers on the naming of each of the Oracle Clinical objects. These objects include discrete value groups, questions, question groups, DCMs, DCIs, Procedures, Where clauses, and queries. As input, the function takes a code for the type of object and the name being validated. If the validation succeeds, the function returns NULL and the user can continue.

If the validation fails, the function returns a varchar2 character string, that contains an error message. The error message is displayed in the status line of the form. This function is called by the following forms that validate the name of the object being created. You can add your own validation to enforce your company naming standards as an example:

• Maintain DVG

• Maintain Questions

• Maintain Question Groups

• Maintain DCMs

• Maintain DCIs

• Maintain Procedures (both Derivation and Validation)

• Maintain Copy Groups

• Maintain Where Clauses

• Maintain Labs

• Mass Changes

ValidateDocument

This function is called by Validation Procedures on the document number in the CRF Log-In form and the Batch Data Load program. The input to the function is the document number being validated. If the validation succeeds, the function returns NULL; otherwise, the function returns the error message the user defined in the package.

DeriveDocumentNumber

With this function you can derive CRF document numbers from the key fields entered in the Log-In form and the Batch Data Load program. If you modify this function, you must review the argument list, reapply the modifications, then reinstall the package. Inputs are:

• Key fields from the CRF and their ID numbers: Study, Clinical_Study_ID, Patient, Patient_Position_ID, Investigator, Investigator_ID, Site, Site_ID, DCI, DCI_ID, Event, Clin_Plan_Eve_ID, and Subevent

• Indicator of test or production mode date: RXC_ENV_TYPE

The function returns NULL if the derivation is not possible; otherwise it returns the derived document number. Document numbers generated by this procedure are not validated against the ValidateDocument function; they are assumed to be valid.

DefaultBookPageNumber

This function assigns the correct page number to the first page of a DCI in the DCI book. The function returns zero if the operation is successful, or a non-zero error message number. The message number (message_topic_id) is used by the Maintain DCI Books to display the appropriate error message from the message_topics table. It uses the TrimPageNumber function and has these input parameters:

• first page number of the previous DCI in the DCI book

• numbering scheme of the previous DCI

• number of expected pages

The function's one output parameter corresponds to the default page number that represents the first page number of a DCI in the DCI book.

TrimPageNumber

This function trims the page number off its suffix according to the numbering scheme. It is called each time Oracle Clinical needs to add one unit to a page number and each time Oracle Clinical needs to assign a default page number.

There are two input parameters:

• page number to be trimmed

• numbering scheme according to which the page number needs to be trimmed

There are two output parameters:

• the result of the trimming operations

• Y when the other output parameter contains a numeric value; N otherwise

Examples of parameter values:

ChangePageStatus

This function sets the client-specific status of a new page, depending on the current page status, the RDCI status, and the blank flag, according to user-defined rules. It returns NULL, if successful; otherwise, it returns an error message.

AddPhysPageNumber

This function adds a number to a page number, according to the numbering scheme— when DCIs are added to a DCI book, and also when pages assigned at data entry correspond to one of the following unplanned events: the DCI is not in the DCI book; the DCI is present in the DCI book but not for the specific visit; or the subevent number is zero.

There are three input parameters:

• page number to which the number must be added

• number to be added to the page number

• numbering scheme

There is one output parameter: the page number after the operation.