21 Mappings

This is a public interface for mapping-related operations; including—creating and modifying mappings at the Table Descriptor and Column levels, creating a Table Descriptor from a Table instance, or a Table instance from a Table Descriptor, and mapping the two. It also includes functions for getting the unique PRREF_ID and PRREF_VER for the executables and Business Areas that contain Table Descriptors. You need these identifiers to run most of the mapping APIs.

This chapter contains the following section:

Create and Modify Mappings

This section contains the following topics:

Map a Column

Use this API to map a Table Descriptor's Columns to a Table instance's Columns using default criteria such as Name, Data Type, and Length. Before you run this API you must map the Table Descriptor and instance and note the Mapping ID and version.

Name

CDR_PUB_DF_MAPPING.GenerateDefaultMapping

Signature

PROCEDURE GENERATEDEFAULTMAPPING( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_MAPPING_NAMING  IN    CDR_NAMING_VERSION_OBJ_TYPE 
); 

Parameters

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

PI_MAPPING_NAMING Mandatory) This is a parameter of table type CDR_NAMING_VERSION_OBJ_TYPE. Enter values to identify the Mapping that you want to extend to the Column level.

The following attributes are required: OBJ_ID and OBJ_VER.

Map a Table Descriptor to a Table Instance

Use this API to map a Table Descriptor to a Table instance. You specify the Table Descriptor and Table instance and the API maps the columns if it is possible.

Name

CDR_PUB_DF_MAPPING.AutoMapTableDesc

Signature

PROCEDURE AUTOMAPTABLEDESC( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_PRREFID  IN    CDR_PROGRAM_REFS.PRREF_ID%TYPE, 
  PI_PRREFVER  IN    CDR_PROGRAM_REFS.PRREF_VER%TYPE, 
  PI_TDOBJ  IN    CDR_NAMING_VERSION_OBJ_TYPE, 
  PI_TIOBJ  IN    CDR_NAMING_VERSION_OBJ_TYPE 
); 

Parameters

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

  • PI_PRREFID (Mandatory) Enter the PRREF_ID of the executable object—Program, Load Set, Data Mart, Business Area that owns the Table Descriptor that you want to map. (Use other APIs in this package to get the PRREF_ID.)
  • PI_PRREFVER (Mandatory) Enter the PRREF_VER of the executable object or Business Area that owns the Table Descriptors that you want to map. (Use other APIs in this package to get the PRREF_VER.)
  • PI_TDOBJ (Mandatory) This is a parameter of table type CDR_NAMING_VERSIONS_OBJ_TYPE that contains CDR Naming Version attributes. Enter values to identify the Table Descriptor you want to map.

    The required attributes are: COMPANY_ID, OBJ_ID, OBJ_VER.

  • PI_TIOBJ (Mandatory) This is a parameter of table type CDR_NAMING_VERSIONS_OBJ_TYPE that contains CDR Naming Version attributes. Enter values to identify the Table instance to which you want to map the Table Descriptor.

    The required attributes are: COMPANY_ID, OBJ_ID, OBJ_VER.

Get a Table Instance ID

Use this API to get the Object ID of the table instance that is mapped to a particular table descriptor. You can invoke this function within a PLSQL program.

While transforming source data into target data in Oracle Health Sciences Data Management Workbench (DMW), it is needed at times, to flag target data. The DMW API used to assign flags to target data requires the Obj ID of the Table Instance as input. This API performs the task of getting the required Obj ID of the target Table Instance, in order that the Flag-related DMW APIs can be invoked.

Name

CDR_PUB_DF_MAPPING.Get_Tab_Inst_ID

Signature

FUNCTION GETTABINSTID( 
PI_TABDESCNAME  IN  VARCHAR2, 
)RETURN NUMBER;

Return

Type NUMBER

Parameters

This API has the following parameter:

PI_TABDESCNAME (Mandatory). Enter the Oracle Name of the Table Descriptor whose mapped Table Instance ID you want to retrieve.

Create a Table Descriptor from a Table Instance

Use this API to create a Table Descriptor from an existing Table instance and map the two.

Name

CDR_PUB_DF_MAPPING.CreateTabDescFromTabInst

Signature

PROCEDURE CREATETABDESCFROMTABINST( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_PRREFID  IN    CDR_PROGRAM_REFS.PRREF_ID%TYPE, 
  PI_PRREFVER  IN    CDR_PROGRAM_REFS.PRREF_VER%TYPE, 
  PI_TIOBJ  IN    CDR_NAMING_VERSION_OBJ_TYPE 
); 

Parameters

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

  • PI_PRREFID (Mandatory) Enter the PRREF_ID of the executable object—Program, Load Set, Data Mart or Business Area, in which you want to create a Table Descriptor. (Use other APIs in this package to get this value.)
  • PI_PRREFVER (Mandatory) Enter the PRREF_VER of the executable object or Business Area in which you want to create a Table Descriptor. (Use other APIs in this package to get this value.)
  • PI_TIOBJ (Mandatory) This is a parameter of table type CDR_NAMING_VERSIONS_OBJ_TYPE that contains CDR Naming Version attributes.

    Enter values for the Table instance from which you want to create a Table Descriptor. The required attributes are: COMPANY_ID, OBJ_ID, OBJ_VER.

Create a Table Instance from a Table Descriptor

Use this API to create a Table instance from a Table Descriptor and map the two.

Name

CDR_PUB_DF_MAPPING.CreateTabInstFromTabDesc

Signature

PROCEDURE CREATETABINSTFROMTABDESC( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_PRREFID  IN    CDR_PROGRAM_REFS.PRREF_ID%TYPE, 
  PI_PRREFVER  IN    CDR_PROGRAM_REFS.PRREF_VER%TYPE, 
  PI_TDOBJ  IN    CDR_NAMING_VERSION_OBJ_TYPE, 
  PI_INSTANCESUBTYPEID  IN    CDR_NAMINGS.OBJECT_SUBTYPE_ID%TYPE 
); 

Parameters

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

  • PI_PRREFID (Mandatory) Enter the PRREF_ID of the executable object instance—Program, Load Set, Data Mart, OR Business Area instance that owns the Table Descriptors that you want to map. (Use other APIs in this package to get this value.)
  • PI_PRREFVER (Mandatory) Enter the PRREF_VER of the executable object instance or Business Area instance that owns the Table Descriptors that you want to map. (Use other APIs in this package to get this value.)
  • PI_TDOBJ (Mandatory) This is a parameter of table type CDR_NAMING_VERSIONS_OBJ_TYPE that contains CDR Naming Version attributes.

    Enter values for the Table Descriptor from which you want to create a Table Descriptor. The required attributes are: COMPANY_ID, OBJ_ID, OBJ_VER.

  • PI_INSTANCESUBTYPEID (Mandatory) Enter a value for the Table instance's subtype.

Modify a Mapping Column

Use this API to update a mapping at the Column level. You can map currently unmapped Columns, map Table Descriptor Columns to different Columns in the Table instance, change the default value for any Column, and supply or change a format string for a Column. This API enforces all Column mapping rules.

Name

CDR_PUB_DF_MAPPING.UpdateMappingColumns

Signature

PROCEDURE UPDATEMAPPINGCOLUMNS( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_MAPPING_COLUMNS  IN    CDR_MAPPING_COLUMNS_COLL 
); 

Parameters

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

PI_MAPPING_COLUMNS (Mandatory) This is a collection of CDR_MAPPING_COLUMNS_TYPEs. For each Column that you want to modify, initialize a CDR_MAPPING_COLUMNS_TYPE and then extend the collection. All the attributes are required.

Modify a Mapping at the Table Descriptor Level

Use this API to update a mapping at the Table Descriptor level in accordance with all validation rules.

Name

CDR_PUB_DF_MAPPING.ModifyMapping

Signature

PROCEDURE UPDATEMAPPINGCOLUMNS( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_MAPPING_COLUMNS  IN    CDR_MAPPING_COLUMNS_COLL 
); 

Parameters

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

PI_MAPPING_COLUMNS (Mandatory) This is a collection of CDR_MAPPING_COLUMNS_TYPEs. For each Column that you want to modify, initialize a CDR_MAPPING_COLUMNS_TYPE and then extend the collection. All the attributes are required.

Get a PRREF_ID for an Executable in a Workflow

Use this API to get the PRREF_ID and PRREF_VER for an executable Object instance contained in a Workflow. You need these values to run Mapping APIs.

Name

CDR_PUB_DF_MAPPING.GetPRREFIDforObjUnderWF

Signature

PROCEDURE GETPRREFIDFOROBJUNDERWF( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_SIMPLEOBJ  IN    CDR_BASE_OBJ_TYPE, 
  PI_RSIOBJ  IN    CDR_BASE_OBJ_TYPE, 
  PI_WFIOBJ  IN    CDR_BASE_OBJ_TYPE, 
  PO_PRREFID  OUT    CDR_PROGRAM_REFS.PRREF_ID%TYPE, 
  PO_PRREFVER  OUT    CDR_PROGRAM_REFS.PRREF_VER%TYPE 
); 

Parameters

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

  • PI_SIMPLEOBJ (Mandatory) This is a parameter of table type CDR_BASE_OBJ_TYPE. Enter values to identify the Program, Load Set, or Data Mart instance whose PRREFID you need.

    The following attributes are required: COMPANY_ID, OBJ_ID, OBJ_VER.

  • PI_RSIOBJ (Optional) If you are getting the PRREFID for a Program instance contained in a Report Set that within a Workflow, enter values to identify the Report Set instance in which the Program instance is located. This is a parameter of table type CDR_BASE_OBJ_TYPE.

    The following attributes are required: COMPANY_ID, OBJ_ID,OBJ_VER.

  • PI_WFIOBJ (Mandatory) This is a parameter of table type CDR_BASE_OBJ_TYPE. Enter values to identify the Workflow instance that contains the Program, Load Set, or Data Mart instance whose PRREFID you need.

    The following attributes are required: COMPANY_ID, OBJ_ID,OBJ_VER.

  • PO_PRREFID This output parameter returns the executable object instance's PRREF_ID.
  • PO_PRREFVER This output parameter returns the executable object instance's PRREF_VER.

Get a PRREF_ID for an Object in a Work Area

Use this API to get the PRREF_ID and PRREF_VER for a Program, Load Set, Data Mart, or Business Area instance contained directly in a Work Area. You need these values to run Mapping APIs.

Name

CDR_PUB_DF_MAPPING.GetPRREFIDforSimpleObject

Signature

PROCEDURE GETPRREFIDFORSIMPLEOBJECT( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_SIMPLEOBJ  IN    CDR_BASE_OBJ_TYPE, 
  PO_PRREFID  OUT    CDR_PROGRAM_REFS.PRREF_ID%TYPE, 
  PO_PRREFVER  OUT    CDR_PROGRAM_REFS.PRREF_VER%TYPE 
);

Parameters

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

  • PI_SIMPLEOBJ (Mandatory) This is a parameter of table type CDR_BASE_OBJ_TYPE. Enter values to identify the Program, Load Set, Data Mart, or Business Area instance whose PRREFID you need in order to map its Table Descriptors.

    The following attributes are required: COMPANY_ID,OBJ_ID,OBJ_VER.

  • PO_PRREFID This output parameter returns the Program, Load Set, Data Mart, or Business Area instance's PRREF_ID.
  • PO_PRREFVER This output parameter returns the Program, Load Set, Data Mart, or Business Area instance's PRREF_VER.

Get a PRREF_ID for a Program in a Report Set

Use this API to get the PRREF_ID and PRREF_VER for a Program instance contained in a Report Set. You need these values to run Mapping APIs.

Name

CDR_PUB_DF_MAPPING.GetPRREFIDforPgmUnderRSE

Signature

PROCEDURE GETPRREFIDFORPGMUNDERRSE( 
  P_API_VERSION  IN    NUMBER, 
  P_INIT_MSG_LIST  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_COMMIT  IN    VARCHAR2 := CDR_PUB_DEF_CONSTANTS.G_FALSE, 
  P_VALIDATION_LEVEL  IN    NUMBER := CDR_PUB_DEF_CONSTANTS.G_VALID_LEVEL_FULL, 
  X_RETURN_STATUS  OUT    VARCHAR2, 
  X_MSG_COUNT  OUT    NUMBER, 
  X_MSG_DATA  OUT    VARCHAR2, 
  PI_SIMPLEOBJ  IN    CDR_BASE_OBJ_TYPE, 
  PI_RSIOBJ  IN    CDR_BASE_OBJ_TYPE, 
  PO_PRREFID  OUT    CDR_PROGRAM_REFS.PRREF_ID%TYPE, 
  PO_PRREFVER  OUT    CDR_PROGRAM_REFS.PRREF_VER%TYPE 
);

Parameters

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

  • PI_SIMPLEOBJ (Mandatory) This is a parameter of table type CDR_BASE_OBJ_TYPE. Enter values to identify the Program instance whose PRREFID you need in order to map its Table Descriptors.

    The following attributes are required: COMPANY_ID,OBJ_ID,OBJ_VER.

  • PI_RSIOBJ (Mandatory) This is a parameter of table type CDR_BASE_OBJ_TYPE. Enter values to identify the Report Set instance that contains the Program instance whose PRREFID you need.

    The following attributes are required: COMPANY_ID, OBJ_ID AND OBJ_VER.

  • PO_PRREFID Output Prref_Id
  • PO_PRREFVER Output Prref Ver