Transformations

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

Modify Transformation Maps

Use this API to modify model, table and column level transformation maps.

Name

DME_PUB_XFORM_MAP.ModifyTransformationMap

Signature

PROCEDURE MODIFYTRANSFORMATIONMAP
(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 of the transformation map. It can be specified as '$MAPTYPE$DEFAULT'.
- XFORM_TYPE Enter map_Type of transformation map being modified. For table map, same of modified map type can be:
  - $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 Enter either: SYSTEM or CUSTOM.
- PROGRAM_ID Enter the program object id, if table level transformation of custom type being modified, otherwise NULL.
- PROGRAM_VER Enter the program object version, if table level transformation of custom type being modified, otherwise NULL.
- PIVOT_COLUMN_ID Enter Object Id of a Column, if table level pivot transformations being modified, otherwise NULL.
- PIVOT_COLUMN_VER Enter Object Version of a Column, if table level pivot transformations being modified, otherwise NULL.
- OPERATION_TYPE Enter operation type as '$OPER$MODIFY'.
- MAP_ENTITY_COLL This attribute is a collection of DME_MAP_ENTITY_OBJ_TYPE object type attributes. All attributes are required including identifying attributes along with required modifications. The operation_type should be passed as '$OPER$CREATE', '$OPER$MODIFY' or '$OPER$REMOVE' depending upon the modification required for a map entity.

The EXPR_OBJ_TYPE is one of the attribute in DME_MAP_ENTITY_OBJ_TYPE object type. This 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 Create or Modify an Expression, for details.

JOIN_COLL - This is a parameter of collection type CDR_DM_JOIN_OBJ_COLL and CDR_DM_JOIN_OBJ_COLL is a table of CDR_DM_JOIN_OBJ_TYPE object type. This collection is required for modifying table level transformations of 'Join' Type.
The attributes of CDR_DM_JOIN_OBJ_TYPE required for this API, including the modifications which are required in the join condition, are:
- COMPANY_ID
- OBJ_ID
- OBJ_VER
- TABLE_NAME
- FK_TABLE_NAME
- TAB_COMPANY_ID
- TAB_OBJ_ID
- TAB_ALIAS
- TAB_MAP_ENTITY_ID
- FK_TAB_COMPANY_ID
- FK_TAB_OBJ_ID
- FK_TAB_ALIAS
- FK_TAB_MAP_ENTITY_ID
- TD_OUTERJOIN_RC
- FK_TD_OUTERJOIN_RC
- DM_JOIN_COL_OBJ_COLL

The DM_JOIN_COL_OBJ_COLL attribute 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.

The attributes of CDR_DM_JOIN_COL_OBJ_TYPE required for this API, including the modifications which are required in the join condition, are:

COMPANY_ID
DM_JOIN_OBJ_ID
DM_JOIN_OBJ_VER
TAB_COMPANY_ID
TAB_OBJ_ID
TAB_ALIAS
TABLE_NAME
TAB_COL_COMPANY_ID
TAB_COL_OBJ_ID
TABLE_COLUMN_NAME
FK_TAB_COMPANY_ID
FK_TAB_OBJ_ID
FK_TAB_ALIAS
FK_TABLE_NAME
FK_TAB_COL_COMPANY_ID
FK_TAB_COL_OBJ_ID
FK_TABLE_COL_NAME
POSITION
JOIN_OPERATOR_RC

The JOIN_OPERATOR_RC 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

Mark Table Maps Not Used

Use this API to mark table mappings as Not Used.

Name

DME_PUB_XFORM_MAP.MarkTableMapNotUsed

Signature

PROCEDURE MARKTABLEMAPNOTUSED
( 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,   PI_MAPOBJID IN CDR_NAMING_VERSIONS.OBJ_ID%TYPE,   PI_MAPOBJVER IN CDR_NAMING_VERSIONS.OBJ_VER%TYPE
);

Parameters

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

PI_MAPOBJID (Mandatory). Enter object Id of Table Transformation Map.
PI_MAPOBJVER (Mandatory). Enter object Version of Table Transformation Map.

Parent topic: Create and Modify Transformation Maps

Mark Column Maps Not Used

Use this API to mark column mappings as Not Used.

Name

DME_PUB_XFORM_MAP.MarkColumnMapNotUsed

Signature

PROCEDURE MARKCOLUMNMAPNOTUSED
(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,  PI_MAPOBJID IN CDR_NAMING_VERSIONS.OBJ_ID%TYPE,  PI_MAPOBJVER IN CDR_NAMING_VERSIONS.OBJ_VER%TYPE
);

Parameters

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

PI_MAPOBJID (Mandatory). Enter object Id of Column Transformation Map.
PI_MAPOBJVER (Mandatory). Enter object Version of Column Transformation Map.

Parent topic: Create and Modify Transformation Maps

Check In Transformation Maps

Use this API to check in the given transformation map.

Name

DME_PUB_XFORM_MAP.CheckinTransformationMap

Signature

PROCEDURE CHECKINTRANSFORMATIONMAP
(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_NAMING IN OUT NOCOPY CDR_NAMING_VERSION_OBJ_TYPE
);

Parameters

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

PIO_NAMING (Mandatory). This is a parameter of table type CDR_NAMING_VERSION_OBJ_TYPE. Enter values to identify the transformation map that you want to check in. The following attributes are required:
- COMPANY_ID
- OBJ_ID
- OBJ_VER
- NAMESPACE_OBJ_ID
- NAMESPACE_OBJ_VER
- OBJECT_VERSION_NUMBER

Parent topic: Create and Modify Transformation Maps

Check Out Transformation Maps

Use this API to check out the given transformation map.

Name

DME_PUB_XFORM_MAP.CheckoutTransformationMap

Signature

PROCEDURE CHECKOUTTRANSFORMATIONMAP
(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_NAMING IN OUT NOCOPY CDR_NAMING_VERSION_OBJ_TYPE
);

Parameters

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

PIO_NAMING (Mandatory). This is a parameter of table type CDR_NAMING_VERSION_OBJ_TYPE. Enter values to identify the transformation map.
The attributes for this API are:
- COMPANY_ID
- OBJ_ID
- OBJ_VER
- NAMESPACE_OBJ_ID
- NAMESPACE_OBJ_VER
- OBJECT_VERSION_NUMBER

Parent topic: Create and Modify Transformation Maps

Undo Checkout Transformation Map

Use this API to undo check out the given transformation map.

Name

DME_PUB_XFORM_MAP.UndoCheckoutTransformationMap

Signature

PROCEDURE UNDOCHECKOUTTRANSFORMATIONMAP
(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_NAMING IN OUT NOCOPY CDR_NAMING_VERSION_OBJ_TYPE
);

Parameters

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

PIO_NAMING (Mandatory). This is a parameter of table type CDR_NAMING_VERSION_OBJ_TYPE. Enter values to identify the transformation map.

The following attributes are required: COMPANY_ID, OBJ_ID, OBJ_VER, NAMESPACE_OBJ_ID, NAMESPACE_OBJ_VER, OBJECT_VERSION_NUMBER.

Parent topic: Create and Modify Transformation Maps

Auto Map Tables

Use this API to create candidate auto Maps for table & Column transformation mappings. This operation accepts the target data model identifier object details as parameter for which auto maps has to be generated. The API creates the potential auto maps based on Oracle Name, Data Type & Length and Alias Match.

Name

DME_PUB_XFORM_MAP.AutoMapTables

Signature

PROCEDURE AUTOMAPTABLES
(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,  PI_DATAENTITYID IN CDR_NAMING_VERSIONS.OBJ_ID%TYPE,  PI_DATAENTITYVER IN CDR_NAMING_VERSIONS.OBJ_VER%TYPE
);

Parameters

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

PI_DATAENTITYID (Mandatory). Enter the object Id of the model transformation map on which auto map has to be performed.
PI_DATAENTITYVER (Mandatory). Enter the object Version of the model transformation map on which auto map has to be performed.

Parent topic: Create and Modify Transformation Maps

Accept Table Mappings

Use this API to create persistent Auto Maps by accepting the auto generated table and column transformation mappings created from dme_pub_xform_map.autoMapTables public API. User refers to DME_PUB_XFM_AUTOMAPS_V view to populate the attributes of input collection required as a parameter.

Name

DME_PUB_XFORM_MAP.AcceptTableAutoMappings

Signature

PROCEDURE ACCEPTTABLEAUTOMAPPINGS
(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_TABLEAUTOMAPCOLL IN OUT NOCOPY DME_XFORM_AUTO_MAP_COLL
);

Parameters

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

PIO_TABLEAUTOMAPCOLL (Mandatory). This is a parameter of table type collection DME_XFORM_AUTO_MAP_COLL of DME_XFORM_AUTO_MAP_TYPE table type.
All attributes are required including:
- COMPANY_ID
- MAP_ID
- MAP_VER
- MAP_ENTITY_ID
- MAP_ENTITY_VER
- CANDIDATE_FLAG_RC - with the possible values:
  - $YESNO$YES
  - $YESNO$NO - use this value for auto map suggestions which are applicable to save as real maps.

Parent topic: Create and Modify Transformation Maps

Auto Map Columns

Use this API to create candidate auto Maps for Column transformation mappings. This operation accepts the target table object identifier details as parameter for which auto maps has to be generated. The API creates the potential auto maps based on Oracle Name, Data Type & Length and Alias Match.

Name

DME_PUB_XFORM_MAP.AutoMapColumns

Signature

PROCEDURE AUTOMAPCOLUMNS
( 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,    PI_DATAENTITYID IN CDR_NAMING_VERSIONS.OBJ_ID%TYPE,    PI_DATAENTITYVER IN CDR_NAMING_VERSIONS.OBJ_VER%TYPE
);

Parameters

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

PI_DATAENTITYID (Mandatory). Enter the object Id of the target table on which auto map has to be performed.
PI_DATAENTITYVER (Mandatory). Enter the object Version of the target table.

Parent topic: Create and Modify Transformation Maps

Accept Column Mappings

Use this API to create persistent Auto Maps by accepting the auto generated column transformation mappings.

Name

DME_PUB_XFORM_MAP.AcceptColumnAutoMappings

Signature

PROCEDURE ACCEPTCOLUMNAUTOMAPPINGS
(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_COLUMNAUTOMAPCOLL IN OUT NOCOPY DME_XFORM_AUTO_MAP_COLL
);

Parameters

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

PIO_COLUMNAUTOMAPCOLL (Mandatory). This is a parameter of table type collection DME_XFORM_AUTO_MAP_COLL of DME_XFORM_AUTO_MAP_TYPE table type. All attributes are required including:

COMPANY_ID
MAP_ID
MAP_VER
MAP_ENTITY_ID
MAP_ENTITY_VER
CANDIDATE_FLAG_RC - with the possible values:
- $YESNO$YES
- $YESNO$NO - use this value for auto map suggestions which are applicable to save as real maps.

Parent topic: Create and Modify Transformation Maps

Upgrade Transformation Map

Use this API to upgrade the maps defined at model level.

Name

DME_PUB_XFORM_MAP.UpgradeXformMap

Signature

PROCEDURE UPGRADEXFORMMAPS
(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
);

Parameters

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

PI_XFORMMAPCOMPID (Mandatory). Enter the COMPANY_ID for the transformation map to be upgraded.
PI_XFORMMAPOBJID (Mandatory). Enter the OBJ_ID for the transformation map to be upgraded.
PI_XFORMMAPOBJVER (Mandatory). Enter the OBJ_VER for the transformation map to be upgraded.

Parent topic: Create and Modify Transformation Maps

Install Transformation Map

Use this API to install transformation mappings.

Name

DME_PUB_XFORM_MAP.InstallXform

Signature

PROCEDURE INSTALLXFORM
( 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,    PI_MAPCOMPANYID IN CDR_NAMINGS.COMPANY_ID%TYPE,    PI_MAPOBJID IN CDR_NAMINGS.OBJ_ID%TYPE,    PI_MAPOBJVER IN CDR_NAMING_VERSIONS.OBJ_VER%TYPE,    PI_CONTEXT IN VARCHAR2 DEFAULT '$LIFECYCLE$DEV',    PO_JOBID OUT NOCOPY CDR_JOBS.JOB_ID%TYPE
);

Parameters

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

PI_MAPCOMPANYID (Mandatory). Enter values for the company Id for Model Transformation Map.
PI_MAPOBJID (Mandatory). Enter values for the object Id for Model Transformation Map.
PI_MAPOBJVER (Mandatory). Enter values for the object Version for Model Transformation Map.
PI_CONTEXT (Mandatory). Enter values for the lifecycle context. The possible values are:
- $LIFECYCLE$DEV (also the default value)
- $LIFECYCLE$QC
- $LIFECYCLE$ PROD
PO_JOBID This is the out parameter as job id generated from installation of given transformation map. Installation of Transformation Mapping submits a job. So this output parameter returns a JOB_ID for Transformation Mapping Batch installation.

Parent topic: Create and Modify Transformation Maps

Remove Transformation Map

Use this API to remove the transformation map at Model, Table or Column Level.

Name

DME_PUB_XFORM_MAP.RemoveTransformationMap

Signature

PROCEDURE REMOVETRANSFORMATIONMAP
(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_NAMING IN OUT NOCOPY CDR_NAMING_VERSION_OBJ_TYPE
);

Parameters

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

PIO_NAMING (Mandatory). This is a parameter of table type CDR_NAMING_VERSION_OBJ_TYPE. Enter values to identify the transformation map. The following attributes are required:

COMPANY_ID
OBJ_ID
OBJ_VER

Parent topic: Create and Modify Transformation Maps

Validate Transformation Maps

Use this API to validate the transformation maps defined at model and table level. If a transformation map is invalid, the status of transformation map is invalidated to 'Invalid', description field is updated with validation errors and red icon is shown on UI corresponding to invalid transformation maps.

Name

DME_PUB_XFORM_MAP.ValidateXform

Signature

PROCEDURE VALIDATEXFORM
(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_NAMING IN OUT NOCOPY CDR_NAMING_VERSION_OBJ_TYPE
);

Parameters

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

PIO_NAMING (Mandatory). This is a parameter of table type CDR_NAMING_VERSION_OBJ_TYPE. Enter values to identify the transformation map. The following attributes are required:

COMPANY_ID
OBJ_ID
OBJ_VER

Parent topic: Create and Modify Transformation Maps

Update Validation Status

Use this API to modify the validation status.

Name

DME_PUB_XFORM_MAP.UpdateValStatus

Signature

PROCEDURE UPDATEVALSTATUS
(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_NAMING IN OUT NOCOPY CDR_NAMING_VERSION_OBJ_TYPE
);

Parameters

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

PIO_NAMING (Mandatory). This is a parameter of table type CDR_NAMING_VERSION_OBJ_TYPE. All attributes are required including:

COMPANY_ID
OBJ_ID
OBJ_VER
NAMESPACE_OBJ_ID
NAMESPACE_OBJ_VER
OBJECT_TYPE_RC
CHECKED_OUT_FLAG_RC
VALIDATION_STATUS_RC

The new validation status should be assigned to field VALIDATION_STATUS_RC. The possible valid values for validation status are:

$SYSVALDNSTEPS$DEVELOPMENT
$SYSVALDNSTEPS$QUALITYCONTROL
$SYSVALDNSTEPS$PRODUCTION
$SYSVALDNSTEPS$RETIRED

Parent topic: Create and Modify Transformation Maps

Execute Transformation Map

Use this API to execute a Transformation Map.

Name

DME_PUB_XFORM_MAP.ExecuteXform

Signature

PROCEDURE EXECUTEXFORM
(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,  PI_MODELCOMPANYID IN CDR_NAMINGS.COMPANY_ID%TYPE,  PI_MODELOBJID IN CDR_NAMINGS.OBJ_ID%TYPE,  PI_MODELOBJVER IN CDR_NAMING_VERSIONS.OBJ_VER%TYPE,  PI_SUBMISSION_TYPE IN VARCHAR2 DEFAULT '$SUBMISSTYPES$IMMEDIATE',  PI_CONTEXT IN VARCHAR2 DEFAULT '$LIFECYCLE$DEV',  PI_SCHED_START_TS IN DATE DEFAULT NULL,  PI_SCHED_END_TS  IN DATE DEFAULT NULL,  PI_SCHED_REPEAT_INTERVAL IN VARCHAR2 DEFAULT NULL,  PI_SCHED_REPEAT_LIST IN VARCHAR2 DEFAULT NULL,  PI_FORCE_EXECUTION_FLAG_RC IN VARCHAR2 DEFAULT '$YESNO$NO',  PI_RUN_MODE_RC IN VARCHAR2,  PI_TRIGGER_DOWNSTREAM IN VARCHAR2,  PO_JOBID OUT NOCOPY CDR_JOBS.JOB_ID%TYPE
);

Parameters

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

PI_MODELCOMPANYID (Mandatory). Company id of Target Data Model.
PI_MODELOBJID (Mandatory). Object Id of Target Data Model.
PI_MODELOBJVER (Mandatory). Object Version of Target Data Model
PI_SUBMISSION_TYPE. Enter '$SUBMISSTYPES$IMMEDIATE or '$SUBMISSTYPES$SCHEDULED' or '$SUBMISSTYPES$DEFERRED'.
PI_CONTEXT. Lifecycle context values like $LIFECYCLE$DEV, $LIFECYCLE$QC or $LIFECYCLE$PROD.
PI_SCHED_START_TS. Enter start time if submission is scheduled/deferred.
PI_SCHED_END_TS. Enter end time if submission is scheduled.
PI_SCHED_REPEAT_INTERVAL. Enter inerval time if submission is scheduled.
PI_SCHED_REPEAT_LIST. Enter Unit like Hour(s), Day(s), Week(s).
PI_FORCE_EXECUTION_FLAG_RC. Enter '$YESNO$NO or '$YESNO$YES'.
PI_RUN_MODE_RC. Enter '$RUNMODES$FULL' or '$RUNMODES$INCREMENT'
PI_TRIGGER_DOWNSTREAM. Enter '$YESNO$YES' or '$YESNO$NO'.

Parent topic: Create and Modify Transformation Maps

Create Staging Table

Use this API to create a new Staging Table.

Name

DME_PUB_XFORM_MAP.CreateStagingTable

Signature

PROCEDURE CREATESTAGINGTABLE
(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_XFORMMAPCOLL IN OUT NOCOPY DME_XFORM_MAP_EX_COLL,  PI_EXISTINGSECMODELOBJ IN CDR_BASE_OBJ_TYPE
);

Parameters

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

PIO_XFORMMAPCOLL (Mandatory). This is a parameter of collection type DME_XFORM_MAP_EX_COLL and DME_XFORM_MAP_EX_COLL is table of DME_XFORM_MAP_EX_OBJ_TYPE object type.
The collection is populated with both table and column mappings with respective source and target entities. Staging table details are set at table level. Staging table can be created from one or more source tables.

The attributes required for this API from DME_XFORM_MAP_EX_OBJ_TYPE are:
- NAMING: Table type CDR_NAMING_VERSION_OBJ_TYPE
- COMPANY_ID - Enter the Company Id.
- OBJECT_TYPE_RC - Enter the OBJECT_TYPE_RC as '$OBJTYPES$XFORMMAP'
- NAME - Enter the name of user defined staging table in table level mapping.
For Table & Column level mappings following additional attributes are required:
- MAP_LEVEL - Map_Level should be assigned as 'TABLE' for table maps and 'COLUMN' for column maps.
- MAP_TYPE - Map_Type should be passed as '$MAPTYPE$DEFAULT' for both table and column level.
- XFORM_TYPE - Map_Type should be passed as '$XFORMTYPE$DIRECT' for table maps.
- DUP_NUM_FLAG - This flag should be passed as '$YESNO$YES' to support duplicates.
- OPERATION_TYPE - The operation type should be passed as '$OPER$CREATE'.
- MAP_ENTITY_COLL - This attribute is a collection of DME_MAP_ENTITY_OBJ_TYPE object type attributes.
For table level maps, this collection should be populated only with one or more Source tables from source data model from which user want to select the columns to use in staging table. For column level maps, this collection should be populated both with Source and Target column entity.

The attributes required for this API from DME_MAP_ENTITY_OBJ_TYPE are:
- DATA_ENTITY_ID - Enter Table Object Id for Table Level and or Column Object Id for Column Level Source entities. Leave blank for Column level target entity.
- DATA_ENTITY_VER - Enter Table Object Version for Table Level and or Column Object Version for Column Level Source entities. Leave blank for Column level target entity.
- ALIAS - Enter alias as Target Column Name for Column Level Target Map entity.
- MAP_RELATION - Map_relation should be passed as 'SOURCE' for source map entities and 'TARGET' for target map entities. The Table level mapping will only have 'SOURCE' map relation entities.
- PRIMARY_KEY_FLAG - This value should be passed as '$YESNO$YES' for the target column entities which are applicable to be primary key in staging table.
- OPERATION_TYPE - The operation type should be passed as '$OPER$CREATE' for all source and target entities.
PI_EXISTINGSECMODELOBJ (Mandatory). This is a parameter of CDR_BASE_OBJ_TYPE. Enter identifying attributes of the Target data model where you want to create the staging table. The following attributes are required:
- COMPANY_ID
- OBJ_ID
- OBJ_VER

Parent topic: Create and Modify Transformation Maps

Validate Expression

Use this API to validate user defined map column or criteria expression.

Name

DME_PUB_XFORM_MAP.ValidateExpression

Signature

PROCEDURE VALIDATEEXPRESSION
(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,  PI_OBJID IN CDR_NAMINGS.OBJ_ID%TYPE,  PI_OBJVER IN CDR_NAMING_VERSIONS.OBJ_VER%TYPE,  PI_EXPROBJ IN DME_XFORM_EXPR_OBJ_TYPE
);

Parameters

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

PI_OBJID (Mandatory). Enter the object Id of the model transformation map in which expression is applied for one of the map entity. In case of Create operations, map will not exist in DB. Pass the table definition id from which column has been selected into the expression. Pass NULL, in case, no column is used in expression text.
PI_OBJVER (Mandatory). Enter the object Version of the model transformation map or table definition from which column is used in expression. Pass NULL, in case there is no column used in expression.
PI_EXPROBJ (Mandatory). This parameter is a object type of DME_XFORM_EXPR_OBJ_TYPE. Refer to the following sectionCreate or Modify an Expression, for more details.

Parent topic: Create and Modify Transformation Maps

Refresh Static Packages

Run this API after you add, modify, or delete a static package to be used in a transformation or validation check. This is required for the changes to take effect.

Name

DME_PUB_XFORM_MAP.populateStaticPackages

Signature

PROCEDURE dme_pub_xform_map.populateStaticPackages
  ( 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);

Parameters

This API has standard parameters (see Standard Parameters).

Parent topic: Create and Modify Transformation Maps