8 Custom programs and functions

• Avoid using custom programs
  
• To create table descriptions for a custom program, copy existing table definitions in LSH
  
• Use cloning to create study-specific custom programs
  
• Include only columns for the Listing user interface in a custom program for a validation check
  
• Specify a static reference for secondary source code
  

Avoid using custom programs

• Recommendation: Whenever possible, define a transformation using the DMW user interface, including standard Oracle SQL and custom functions, as opposed to custom programs.

• Rationale: If you use custom programs, you are responsible for the coding. This isn't a scalable approach. In addition, custom programs are not as re-usable as transformations that you define in the DMW user interface.

• Additional information: For more information about using custom functions, see the Guidance for DMW Implementation and Configuration document.

  The only scenario in which we recommend using a custom program is when defining the Filter Drive table, which is marked with the table SUBJECTVISIT SDTM Identifier. In an ideal study design, that is the only custom program used.

To create table descriptions for a custom program, copy existing table definitions in LSH

• Recommendation: To create table descriptors for a custom program, in LSH, copy the study's existing target and source table definitions to the local application area. Do not use the existing definitions from the study application area for the custom program.

  Note:

  You do not need to do this if the source and target tables are the same table definition.

• Rationale: The study's existing target and source table definitions typically provide the most accurate description of the tables. In addition, following this guidance allows for a consistent approach when underlying table definitions are updated.

• Additional information: When you reference a study-specific table to create a table descriptor, make sure that the table descriptor does not reference the study, and that you copy the definition to the local application area.

  This method is a reliable, efficient, and repeatable way to create table descriptors. If you need to update or recreate them, you can recreate the exact process and the definition is a reliable description of the circumstance in the study.

  If you used the same definition for the source and target tables, load an MDD file that describes the table as the table descriptor for both source and target.

Use cloning to create study-specific custom programs

• Recommendation: To create a study-specific custom program, clone an existing custom program to a study-specific application area in the DMW_UTIS domain, and update the clone with the study-specific information.

• Rationale: When you clone a custom program, you can update table descriptors that are specific to a particular study without modifying the initial custom program or creating one from scratch.

• Additional information: Do not use one application area as a common repository for all studies. Instead, use LSH cloning to duplicate the core program definition for specific studies.

Include only columns for the Listing user interface in a custom program for a validation check

• Recommendation:

• Rationale: When you define a custom program for a validation check, include only the columns that you want to appear in the Listings user interface. Additional columns can cause processing issues.

• Additional information: You must include PK and _SKEY columns in the custom program.

Specify a static reference for secondary source code

• Recommendation: When you add secondary source code to a custom program in LSH, make sure to select Yes for the Static Reference field.

  Note:

  Yes is not selected by default.

• Rationale: If you do not specify a static reference, LSH defines a new definition, which is typically not the intent of secondary source code.