1. Study Setup Guide
  2. Create custom programs, listings, and filters, and set up reference data
  3. Create custom programs
  4. Create a custom program for a validation check

Create a custom program for a validation check

To create a custom program for a validation check:

  1. Log in to Oracle LSH, select its Applications tab if it is not already selected, and navigate to the DMW_UTILS domain under DMW_DOMAIN.

  2. In the DMW_UTILS domain, navigate to the application area your administrator has set up for storing custom programs of this type, and click Manage Definitions. Verify that you are in the right application area.

  3. From the Create drop-down, select Program and click Go. Enter a name and description and select the Program Type: PLSQL or SAS. Click Apply.

  4. Create the target table that is displayed on the VC Listings page.

    1. In the Table Descriptors subtab, click Add Target From New. The Create Table Descriptors window opens.

    2. Enter values in the required fields. The table name must not be longer than 25 characters.

    3. Add columns. In addition to the columns you need to display the results of the validation check, add one column for each source table with a name like source_table_SKEY to enable data lineage tracing.

      Note:

      Each source table's Oracle name cannot be longer than 25 characters. This is because the target table must contain a column for surrogate key information that contains the source table name.

    4. In the Actions drop-down, select Table Instances from Existing Table Descriptors and click Go.

    5. Select the target table descriptor you just created and click Create Table Instance. Confirm.

  5. For each source table: In the Table Descriptors subtab, click Add Target From: Library, then select Create a Table Descriptor from an existing Table definition and click the Icon is a magifying glass.Search icon for the Definition Source field.

  6. In the Search and Select window:

    1. In the Domain field, click the Icon is a magifying glass.Search icon and select DMW_DOMAIN, the appropriate study grouping, and your study.

    2. Select Display Table Definitions Under DataModel. Select the clinical data model and enter the table name if you know it, then click Go.

    3. Select the table. The system returns you to the Create Table Descriptor page with the selected table displayed in the Definition Source field. Click Apply, then click Return.

  7. Click the table name, then click Update, change Is Target to No, click Apply, then click Return.

    Note:

    Each source table's Oracle name cannot be longer than 25 characters. This is because the target table must contain a column for surrogate key information that contains the source table name.

    You can change the table name in the source clinical data model.

  8. Write the program. If you have integrated SAS with Oracle LSH you can click the Launch IDE button from the program page.

  9. In the Source Code subtab, click Add, then select Create a new Source Code definition and instance. Enter all required values. For a SAS program, the File Type should be Program, not Macro.

    Note:

    Source Code names:

    • must not include Oracle or PL/SQL reserved words or special characters; see Avoid special characters and reserved words for details.

    • must include a file extension—for example, .sas for SAS or .sql for PL/SQL.

    • The Oracle name must not be the same as the Oracle name of either a table descriptor or another source code in the same PL/SQL program.

  10. Write the program code.

    • Your program must handle populating all target columns, including populating the new auxiliary SKEY columns with the concatenated value of the internal CDR$SKEY (surrogate key) column in each source table. See Enable data lineage tracing in a custom program.

    • If required, you can call Oracle LSH or Oracle DMW public APIs from your code; see Use APIs.

  11. Upload the file containing your program and click Apply.

  12. Check in the program in Oracle LSH.

  13. Run public API DME_PUB_XFORM_MAP.populateStaticPackages whenever you add, modify, or remove a custom program. This makes the new or changed program available for use or, in the case of removing a program, makes it unavailable.

  14. In Oracle DMW, define the validation check in a batch and enter values for all required fields.

  15. Select Create VC using a Custom Program. The system displays a window where you can select the source tables for the custom validation check program.

  16. When the Select a Program icon appears, click it and select the program. You can use the Query By Example fields above any column to search for all or part of a value in that column. For example, enter %cardio% to search for a program in the Cardiology area your administrator has set up.

  17. Install the validation check batch.

Parent topic: Create custom programs