1. API Guide
  2. Oracle Health Sciences Data Management Workbench APIs
  3. Transformations
  4. Create and Modify Transformation Maps
  5. Create Transformation Maps

Create Transformation Maps

Use this API to create model-, table- and column-level transformation maps. Run this API multiple times to create the model-level mapping first, then each table mapping, and then each column mapping.

Name

DME_PUB_XFORM_MAP.CreateTransformationMap

Signature

PROCEDURE CREATETRANSFORMATIONMAP,
(P_API_VERSION IN VARCHAR2,  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,  PIO_XFORMMAP IN OUT NOCOPY DME_XFORM_MAP_EX_OBJ_TYPE
  );

Parameters

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

  • PIO_XFORMMAP (Mandatory). This is a parameter of DME_XFORM_MAP_EX_OBJ_TYPE object type. The attributes required for this API are:
    • NAMING This is an attribute of table type CDR_NAMING_VERSION_OBJ_TYPE.
      • Company_ID To get your company ID, use CDR_PUB_DEF_FACTORY_UTILS.GetCompanyId.
      • OBJECT_TYPE_RC Enter the OBJECT_TYPE_RC as '$OBJTYPES$XFORMMAP'
      • NAMESPACE_OBJ_ID Enter the object ID of the parent in which you want to create transformation map. Parent object ID will be study ID, model map ID and table map ID for creating a model, table and column map respectively.
      • NAMESPACE_OBJ_VER Enter the parent object version in which you want to create transformation map: study version, model map version and table map version for creating a model, table and column map respectively.
    • MAP_LEVEL (Mandatory) Enter either: MODEL, TABLE, or COLUMN.
    • MAP_TYPE Enter Map_Type as '$MAPTYPE$DEFAULT' for transformation at model, table and column level.
    • XFORM_TYPE For model and column maps enter: '$XFORMTYPE$DIRECT'.

      For table level maps, enter one of the following values based on the table map type:

      • $XFORMTYPE$DIRECT
      • $XFORMTYPE$UNION
      • $XFORMTYPE$JOIN
      • $XFORMTYPE$CUSTOM
      • $XFORMTYPE$PIVOT
      • $XFORMTYPE$UNPIVOT
      • $XFORMTYPE$CUSTOM

      Tip:

      Specify the type of transformation that your custom program actually does, if possible. Use the value $XFORMTYPE$CUSTOM only if the transformation does not do any of the other transformation types. This is to support data lineage tracing.
    • PROGRAM_TYPE When creating a table-level map for a transformation with $XFORMTYPE$CUSTOM, enter either SYSTEM or CUSTOM, depending on the program source. Otherwise enter null.
    • PROGRAM_ID Enter Program Obj_Id while creating a table level map of transformation type as '$XFORMTYPE$CUSTOM', otherwise, enter null.
    • PROGRAM_VER Enter Program Obj_Ver while creating a table level map of transformation type as '$XFORMTYPE$CUSTOM', otherwise, enter null.
    • PIVOT_COLUMN_ID Enter Object Id of the pivot column while creating a table transformations of pivot type, otherwise, enter null.
    • PIVOT_COLUMN_VER Enter Object Version of the pivot column while creating a table transformations of pivot type, otherwise, enter null.
    • OPERATION_TYPE Enter operation type as '$OPER$CREATE'.
    • MAP_ENTITY_COLL This attribute is a collection of DME_MAP_ENTITY_OBJ_TYPE object type attributes. The following attributes are required:
      • DATA_ENTITY_ID For transformation map at model level, enter Source Data Model Object Id for Source Map Entity and Target Data Model Object ID for Target Map Entity.

        For transformation map at table level, enter Source Table Definition Object Id for Source Map Entity and Target Table Definition Object ID for Target Map Entity.

        For transformation map at column level, enter Source Column Object Id for Source Map Entity and Target Column Object ID for Target Map Entity.

        Tip:

        If you are mapping multiple tables or columns, pass the source and target Object IDs through a loop to enter values one after the other and commit to the database at the end.
      • DATA_ENTITY_VER Enter Object Version corresponding to the object selected for DATA_ENTITY_ID population.
      • MAP_RELATION Map_relation should be passed as 'SOURCE' for source map entities and 'TARGET' for target map entities.
      • EXPR_OBJ_TYPE This parameter is a object type of DME_XFORM_EXPR_OBJ_TYPE. Pass the expression details for source filter for source tables in case of table level transformations. Refer to the following section Create or Modify an Expression, for more details.
      • OPERATION_TYPE The operation type should be passed as '$OPER$CREATE' for all source and target entities.
    • JOIN_COLL This is a parameter of collection type CDR_DM_JOIN_OBJ_COLL and CDR_DM_JOIN_OBJ_COLL is table of CDR_DM_JOIN_OBJ_TYPE object type. This collection is required for creating table level transformations of 'Join' Type.The following attributes are required for join transformations:
      • COMPANY_ID - Company Id
      • TAB_COMPANY_ID -Table Company Id
      • TAB_OBJ_ID - Table Obj Id
      • TAB_ALIAS - Table Alias
      • FK_TAB_COMPANY_ID - Enter the Company ID of the foreign key table.
      • FK_TAB_OBJ_ID - Enter the Object ID of the foreign key table.
      • FK_TAB_ALIAS - Alias for second table
      • TD_OUTERJOIN_RC- Set to '$YESNO$YES' if outer joined defined on first table, otherwise '$YESNO$NO'.
      • FK_TD_OUTERJOIN_RC- Set to '$YESNO$YES' if outer joined defined on second table, otherwise '$YESNO$NO'.
      • DM_JOIN_COL_OBJ_COLL - This is a parameter of collection type CDR_DM_JOIN_COL_OBJ_COLL and CDR_DM_JOIN_OBJ_COLL is table of CDR_DM_JOIN_COL_OBJ_TYPE object type. This collection is required for populating the join conditions. The attributes required for this API are:
      • COMPANY_ID - Company Id
      • TAB_COMPANY_ID -Table Company Id
      • TAB_OBJ_ID - Table Obj Id
      • TAB_ALIAS - Table Alias
      • TAB_COL_COMPANY_ID -Joined Table Column Company Id
      • TAB_ COL_OBJ_ID - Table Column Obj Id
      • FK_TAB_COMPANY_ID - Second table company Id which is joined with first table
      • FK_TAB_OBJ_ID - Second table obj Id which is joined with first table
      • FK_TAB_ALIAS - Alias for second table
      • FK_TAB_COL_COMPANY_ID -Table Column Company Id from second table which is joined with first table
      • FK_TAB_ COL_OBJ_ID - Table Column Obj Id from second table which is joined with first table
      • POSITION - Sequence of join condition. The sequence should start for first join condition from 1.
      • JOIN_OPERATOR_RC - This refers to type of join condition. This can have the following values:

        $JOINOPER$EQUALS

        $JOINOPER$EQUALS

        $JOINOPER$GREATER

        $JOINOPER$GREATEREQUAL

        $JOINOPER$LESS

        $JOINOPER$LESSEQUAL

        $JOINOPER$NOTEQUALS

Parent topic: Create and Modify Transformation Maps