28 Introduction to Oracle DMW APIs

Oracle Health Sciences Data Management Workbench (Oracle DMW) is built on top of Oracle Life Sciences Data Hub (Oracle LSH) and shares the same database and execution engine. Be sure to read the following sections, which apply to Oracle DMW as well as Oracle LSH APIs:

• Using Application Programming Interfaces
• Reference Information
• Setup Utilities

For information on Oracle DMW functionality and structure, see the user documentation, including information on object ownership (namespaces).

Note:

• Even though an Oracle DMW Study is an Oracle LSH domain, it has additional attributes and you cannot create a study using the API for creating a domain. The API for creating a study is not public. Create studies in the user interface.
• During its initial development, Oracle DMW was known as DME. Therefore many internal names contain the string dme. Think of DME as a synonym for DMW—just as CDR was the early name for Oracle LSH.

This section includes:

• Set Up the Study Environment
  
• Create or Modify an Expression
  

Set Up the Study Environment

The program you are writing must call DME_PUB_INITIALIZATION.SetupAPIStudyEnvironment before it calls any other Oracle DMW API or uses any public view.

This procedure checks that the study ID and lifecycle value you pass in are valid and then uses those values and the study's partition ID for the lifecycle to set SYS_CONTEXT appropriately. Call it again to change the study or lifecycle context. See the following topic for details:

• Initialize a Study and Lifecycle
  

Initialize a Study and Lifecycle

Name

DME_PUB_INITIALIZATION.SetupAPIStudyEnvironment

Signature

PROCEDURE SETUPAPISTUDYENVIRONMENT,
( P_API_VERSION IN NUMBER,   P_INIT_MSG_LIST IN VARCHAR2 DEFAULT CDR_PUB_DEF_CONSTANTS.G_FALSE,   P_COMMIT IN VARCHAR2 DEFAULT CDR_PUB_DEF_CONSTANTS.G_FALSE,   P_VALIDATION_LEVEL IN NUMBER DEFAULT CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL,   X_RETURN_STATUS OUT NOCOPY VARCHAR2,   X_MSG_COUNT OUT NOCOPY NUMBER,   X_MSG_DATA OUT NOCOPY VARCHAR2,   PI_COMPANY_ID IN NUMBER,   PI_STUDY_ID IN NUMBER,   PI_LIFECYCLE IN VARCHAR2,
  );

Parameters

This API has standard parameters (see Standard Parameters) and the following parameters:

• PI_COMPANY_ID (Mandatory). Enter COMPANY_ID of Study.
• PI_STUDY_ID (Mandatory). Enter OBJ_ID of Study.
• PI_LIFECYCLE (Mandatory). Enter Lifecycle context values like $LIFECYCLE$DEV, $LIFECYCLE$QC or $LIFECYCLE$PROD.

Create or Modify an Expression

In Oracle DMW, expressions in both validation checks and transformations are defined in the EXPR_OBJ_TYPE attribute of the DME_MAP_ENTITY_OBJ_TYPE. The EXPR_OBJ_TYPE attribute is of the DME_XFORM_EXPR_OBJ_TYPE object and its required values are:

• EXPRESSION_ID: If you are modifying an expression, enter the expression object ID.
• EXPRESSION_MODE: Enter one:
  • $EXPRMODE$CRITERIA if it is a criteria expression.
  • $EXPRMODE$EXPR if it is a column expression.
• EXPRESSION_TEXT: Enter the string representation of expression, adhering to the following rules:
  • All the values should be single quoted. Numeric values can be applied without single quotes.
  • The column names should be specified in the following format: {ModelName.TableName.ColumnName}. For example, to specify a condition on the 'AGE' column from 'DM' table in 'Source' data model that AGE should be greater than 30, the expression_text should be passed as: {Source.DM.AGE} > 30
  • To specify a user-defined functions, the package should be available in the view DME_PUB_XFM_EXPR_STATIC_PKGS_V. Refer to the static reference details for the package that contains the function.
• EXPRESSION_ITEM_COLL - Use this parameter only if you are calling a function in the expression. This is a parameter of collection type DME_XFORM_EXPR_ITEM_OBJ_COLL which is a table of DME_XFORM_EXPR_ITEM_OBJ_TYPE object type.

  For each expression_text, the collection must be populated with one record with the following attribute assignments:

  PARENT_ROW_ID = -1; ROW_ID=0; ROW_POS=1; ITEM_TYPE_RC=' $EXPITEMTYPE$GROUP'
  

  If user-defined functions are used in expression_text, the collection should be populated with a separate record for each distinct user-defined function used in expression. The following attributes should be assigned for a record used for the user defined function:

  • PARENT_ROW_ID = 0;
  • ROW_POS = 1;

    Note:

    The above two values should always be 0 and 1, respectively.
  • ROW_ID - The row_id must be unique and sequential starting from 1 for each distinct user defined function used in the given expression.
  • ITEM_TYPE_RC =' $EXPITEMTYPE$FUNCTION';

  Enter values for the following two attributes from the DME_XFM_EXPR_STATIC_PKGS_V view.

  • ITEM - Enter the name of the user-defined function.
  • STATIC_REFS - Enter the function ID corresponding to the function used in expression.