System tables

The following tables describe the system tables.

uap_tt_templates

This table contains information about each template in Unica Plan.

Field Type Length Null? Description
template_id INT32 false The unique ID generated by the system for each template. This column is the foreign key to uap_tt_deny_reasons.
object_type INT32 Unica Plan object type. Possible values are the standard marketing object types and any custom marketing object types that are defined in the system.
state INT32 The template state. The following are valid values.
  • 0 = Enabled
  • 1 = Disabled
  • 2 = Deleted
template_name NVARCHAR 50 true The internal, system-generated name of the template.
actual_display NVARCHAR 50 true The default name for the object instance that is created from this template.
display NVARCHAR 192 true The display name of this template. Version 8.6 increased the size of this field to 192.
folder_name NVARCHAR 50 true The name of the template folder that contains the template.
icon_id INT32 The ID of the icon to use for this template. This column is the foreign key to uap_tt_icons.
folder_icon NVARCHAR 300 true This column is not currently in use.
description NVARCHAR 1536 true Template description. Version 8.6 increased the size of this field to 1536.
template_file NVARCHAR 300 true Unica Plan generates the value for this column.
pid_prefix NVARCHAR 30 true The prefix to use in external IDs for object instances that are created from this template.
pid_gen_class NVARCHAR 100 true The name of the Java class that is used to generate the algorithm for external IDs for object instances that are created from this template.
validation_class NVARCHAR 100 true Validation Java class name.
m_template_id INT32 true The ID of the metrics template that is used by this template. This column is the foreign key to uap_tt_m_templates.
use_campaign_code INT32 true
service_url NVARCHAR 200 true Unica Campaign web service URL.
data_map_file NVARCHAR 300 true This column is not in use currently.
metrics_map_file NVARCHAR 300 true Unica Campaign import metrics map file name.
partition_id NVARCHAR 256 true The partition of the Unica Campaign instance in which to create the campaigns for projects that are created from this template. Version 8.6 increased the size of this field.
camp_folder_id NVARCHAR 50 true
workflow_id INT32 true The ID of the workflow template that is used by this template. This column is the foreign key to uap_tt_workflows.
data_map_id INT32 true uap_tt_me_metrics
metric_map_id INT32 true
parent_folder_id INT32 true The ID of the parent template folder.
sec_policy_model INT32 true This column was This column was added in version 8.0.
campaign_template_type INT32 true The type of the Unica Campaign template. The following values are valid.
  • 0 = Not a template
  • 1 = project template that was created before version 8.0
  • 2 = project template that was created in version 8.0
This column was added in version 8.0.
tcs_approval_required INT32 true This column indicates whether top-down TCS approval is enforced and correspondingly, whether the Is Approved column is part of the TCS tab of the templates instances. The following values are valid.
  • 0 = Approval not enforced
  • 1 = Approval enforced
This column was added in version 8.2.0.
autoFillDownCmpDetails INT32 true This flag determines whether project attributes automatically copy into Unica Campaign on campaign project templates. This column was added in version 9.1.0.
health_rule_id INT32 true The health rule definition that is associated with the project template. This column is the foreign key to uap_proj_health_rule.health_rule_id. This column was added in version 9.0.0.
ooo_autoadd_setting INT32 true Defaults to 0. This column was added in version 8.6.0.
rule_xml CLOB true The XML for the workflow template branching.
bpm_setting INT32 true Defaults to 0. This column was added in version 10.0.0.2.

uap_tt_map

This table stores information about each form in your system.

Field Type Length Null? Description
map_id INT32 false Unique identifier generated by the system for each form.
map_name NVARCHAR 50 true Name of the form.
table_name NVARCHAR 256 true Name of the database table that stores the answers users enter in the fields of this form.
key_column NVARCHAR 256 true Column that stores the primary key for the table specified in the table_name column.
key_type NVARCHAR 50 true Data type of the column specified in the key_column column.
state NVARCHAR 50 true State of the form. The following values are valid.
  • Published
  • Non-Published
  • Temporary
This column was added in version 8.0.
root_element NVARCHAR 100 true The only valid value is Details.
description NVARCHAR 1024 true Description of form. This column was added in version 8.0.
user_id INT32 true ID of the user who created the form. This column is the foreign key to uap_user. This column was added in version 8.0.
copy_of INT32 true ID of the form that was copied to make this form. This column is the foreign key to uap_tt_map.map_id. This column was added in version 8.0.
form_type INT32 true Whether the form is a Target Cell Spreadsheet. This column was added in version 8.0.
rule_xml CLOB true

uap_tt_tab

This table stores information about template tabs created by administrators.

Field Type Length Null? Description
template_id INT32 false ID of a template. This column is the foreign key to uap_tt_templates.
map_id INT32 false ID of a form. This column is the foreign key to uap_tt_map.
tab_order INT32 true The numerical position for this form in this template.
name NVARCHAR 100 true The internal, system-generated name of the tab.
display NVARCHAR 100 true Display name of the tab.
map_file NVARCHAR 200 true Name of the form.
page_style NVARCHAR 20 true This column determines whether the form displays as a separate tab or as part of the Summary tab. The following values are valid.
  • summary
  • tab
show_in_wizard NVARCHAR 20 true This column determines whether this tab displays in the wizard. The following values are valid.
  • Y = Yes, display
  • N = No
show_in_request NVARCHAR 20 true This column determines whether this tab displays in project requests. The following values are valid.
  • Y = Yes, display
  • N = No
validation_class NVARCHAR 200 true Grid tabs can have validation; if so, this is the validation class used to validate this grid tab.
rules_file_id INT32 Unique ID for the rules file associated with this grid tab. Foreign key into uap_tt_rules table.
display_rule_id INT32 The display rule specifies the conditions based on which the form is shown expanded to the users; if the rule is not satisfied the form is displayed as collapsed. This column is the foreign key into uap_tt_wf_rule table, rule_id.

uap_tt_att_folders

This table stores information about the folders created to contain template attachments.

Field Type Length Null? Description
folder_id INT32 false The unique identifier generated by the system for each template attachment folder.
template_id INT32 true ID for the template that contains this attachment folder. This column is the foreign key to uap_tt_templates.
parent_folder_id INT32 true ID of the parent folder or category. This column is the foreign key to uap_tt_att_folders.folder_id.
folder_name NVARCHAR 255 true System-generated name of the attachment folder.
folder_display NVARCHAR 255 true Name specified by the creator for the attachment folder.
display_order INT32 true Folder display order.

uap_tt_attachments

This table stores information about each template attachment.

Field Type Length Null? Description
attachment_id INT32 false The unique identifier generated by the system for each template attachment.
folder_id INT32 true ID of the folder that contains the attachment. This column is the foreign key to uap_tt_att_folders.
template_id INT32 true ID for the template that contains the attachment. This column is the foreign key to uap_tt_templates.
attachment_name NVARCHAR 255 true Name of the attachment.
attachment_mime VARCHAR 255 true MIME type of the attachment file.
attachment_ext NVARCHAR 20 true Attachment file extension. For example, .PDF or .DOC.
display_order INT32 true The order in which attachments display in the folder.

uap_tt_ext_tools

This table stores information about the custom links defined in the system.

Field Type Length Null? Description
ext_tool_id INT32 false Unique identifier generated by the system for each custom link.
ext_url NVARCHAR 200 true The URL of the custom link.
template_id INT32 true ID of the template that contains the custom link. This column is the foreign key to uap_tt_templates.
name NVARCHAR 50 true Name of the custom link. This column is used internally.
display NVARCHAR 50 true Display name of the custom link.
description NVARCHAR 200 true Description of the custom link.
show_in_rq NVARCHAR 20 true
display_order INT32 The order in which custom links display.

uap_tt_tool_tab

This table contains information about the tabs on which a custom link is available in a template.

Field Type Length Null? Description
ext_tool_id INT32 false
map_id INT32 false ID of a form. This column is the foreign key to uap_tt_map.
template_id INT32 false ID of a template. This column is the foreign key to uap_tt_templates.

uap_tt_ext_param

This table stores the parameters defined for custom links.

Field Type Length Null? Description
ext_tool_id INT32 false ID for a custom link. This column is the foreign key to uap_tt_ext_tools.
param_name NVARCHAR 200 false Name of a parameter.
param_value NVARCHAR 100 true Value of the parameter.

uap_tt_workflows

This table stores information about workflow templates.

Field Type Length Null? Description
workflow_id INT32 false Unique ID generated by the system for each workflow template.
name NVARCHAR 50 true Workflow template name.
taskcode_prefix NVARCHAR 100 true Used to construct a task code. Task codes are used to uniquely identify tasks, for use with the API.

uap_tt_m_templates

This table stores information about the metrics templates in your system.

Field Type Length Null? Description
m_template_id INT32 false Unique ID generated by the system for each metric template.
name NVARCHAR 50 true Metric template ID name.
display_name NVARCHAR 50 true
description NVARCHAR 300 true Metric template description.

uap_tt_metrics

This table stores information about each metric in your system.

Field Type Length Null? Description
metric_id NVARCHAR 50 false Metric ID name.
display_name NVARCHAR 100 true Metric display name.
description NVARCHAR 300 true Metric description.

uap_tt_roles

This table stores associations between roles and templates.

Field Type Length Null? Description
template_id INT32 false ID of a template. This column is the foreign key to uap_tt_templates.
role_id INT32 false ID of a role. This column is the foreign key to uap_ml_role.
role_name NVARCHAR 255 true Name of the role.
flag_team_mem VARCHAR 1 false Whether this is a team member role.

uap_tt_data_map

This table stores information about the data mapping files in the system.

Field Type Length Null? Description
data_map_id INT32 false
name NVARCHAR 50 true
type INT32 false Type of the data map.

uap_tt_icons

This table stores information about icons.

Field Type Length Null? Description
icon_id INT32 false Unique ID generated by the system for each icon.
name NVARCHAR 50 true Icon display name.
extension NVARCHAR 20 true Icon extension.
listExtension NVARCHAR 20 true Icon list extension.

uap_tt_sec_policy

Field Type Length Null? Description
template_id INT32 false
sec_policy_id INT32 false

uap_tt_export_tab

This table defines, for each template in the system, the tab to be exported when the template is exported. This table was added in version 7.4.0.

Field Type Length Null? Description
template_id INT32 false ID for the template. This column is the foreign key to uap_tt_templates.
tab_name NVARCHAR 100 false Name of the tab.

uap_tt_use_policy

Field Type Length Null? Description
template_id INT32 false
sec_policy_id INT32 false

uap_tt_rules

This table stores the rules file; rules files are used to validate grid tabs.

Field Type Length Null? Description
rule_file_id INT32 false Unique ID generated by the system for each rules file.
rules_name NVARCHAR 100 false Name of the rules file.

uap_tt_folders

Field Type Length Null? Description
folder_id INT32 false
object_type INT32
folder_name NVARCHAR 255 true
folder_display NVARCHAR 255 true
folder_desc NVARCHAR 1024 true
parent_folder_id INT32 true
folder_status VARCHAR 20 true
parent_folder_path NVARCHAR 1024 true
sec_policy_id INT32 true
folder_type VARCHAR 20 true
folder_location NVARCHAR 1024 true

uap_tt_map_info

This table saves details about form attributes. This table was added in 8.0.

Field Type Length Null? Description
map_info_id INT32 false Unique identifier that is generated by the system for each row in the table.
map_id INT32 false ID of the form in which the attribute is used. This column is the foreign key from uap_tt_map table.
group_id INT32 true ID of the attribute group that contains the attribute. This column is the foreign key to uap_tt_map_info_group.map_ info_group_id and uap_tt_tvc_info_group.tvc_ info_group_id.
label NVARCHAR 1024 true Label of the attribute.
element_name NVARCHAR 100 true Internal name of the attribute.
element_type NVARCHAR 50 true User interface type of the attribute.
db_type NVARCHAR 50 true Datatype of the attribute.
db_column_name NVARCHAR 256 true Database column name of the attribute.
max_length INT32 true Maximum length of the attribute value.
lkup_table NVARCHAR 256 true Name of the database table that supplies values from which users can select for this attribute.
lkup_id_column NVARCHAR 256 true Name of the key column in the lookup table for this attribute.
lkup_desc_column NVARCHAR 256 true Name of the column in the lookup table that stores the values to display to the users.
lkup_sort_column NVARCHAR 256 true Name of the column in the lookup table to use to sort the entries that display to the users.
lkup_sort_colDBType NVARCHAR 50 true Datatype of the sort column in the lookup table. Unica Plan retrieves this information from the database.
lkup_sort_ascending NVARCHAR 50 true Sort order for the lookup table. The following values are valid.
  • 0 = Descending
  • 1 = Ascending
formula NVARCHAR 1024 true The formula that to calculate the attribute value. This column applies only to calculated attributes.
url_db_column_name NVARCHAR 256 true This column was added in 8.1 to support the url field in forms.
help_tip NVARCHAR 1024 true Help text for the attribute.
element_message NVARCHAR 1024 true The warning message to display when the user forgets to fill a requiews field.
default_value CLOB true Default value for the attribute.
description NVARCHAR 1536 true Description of the attribute.
default_date_value INT64 true Stores the default date value that is supplied for a Date Select attribute in a form definition. This value is stored as a long millisecond value.
is_required NVARCHAR 1 true This column determines whether the attribute is required. Possible values are Y and N.
  • Y = Yes, required
  • N = No
is_read_only NVARCHAR 1 true This column determines whether the attribute is read-only. Possible values are Y and N.
  • Y = Yes, read-only
  • N = No
element_mprecision INT32 true Number of digits in the decimal part of the number.
is_dependent NVARCHAR 1 true This column determines whether the attribute is dependent. This column applies only to single-select - database and multi-select - database attributes. Possible values are Y and N.
  • Y = Yes, dependent
  • N = No
tab_order INT32 true Order in which the attribute appears on the form.
paramType INT32 true This column is no longer in use.
modifFlag NVARCHAR 65 true Type of update to perform when a form is republished. The following values are valid.
  • 0 = Used in form
  • -3 = Created as local attribute but not placed on form
attribute_type INT32 true The following values are valid.
  • 0 = Local form attribute
  • 1 = Local grid attribute
  • 2 = Global form attribute
  • 3 = Global grid attribute
  • 4 = Global campaign attribute
  • 5 = Global cell attribute
  • 6 = TCS default attribute
  • 7 = TCS read-only attribute
  • 8 = Offer standard attribute
  • 9 = Offer custom attribute
attribute_behavior INT32 true Defaults to 0. This column was dded in version 8.6.0.
is_enabled INT32 true Whether a global attribute is enabled. The following values are valid.
  • 0 = Disabled
  • 1 = Enabled

uap_tt_mo_map_info

This table stores additional properties of single-select object reference attributes, multi-select object reference attributes, and object attribute field reference attributes. This table was added in version 8.0.

Field Type Length Null? Description
mo_map_info_id INT32 false Unique identifier generated by the system for this row.
map_id INT32 false Unique identifier for the form that contains the attribute. This column is the foreign key to uap_tt_map.
map_info_id INT32 false Unique identifier for the attribute. This column is the foreign key to uap_tt_map_info.
mo_type VARCHAR 50 true Type of marketing object to display to users.
template_name NVARCHAR 100 true Name of the marketing object template to use to create or filter marketing objects associated with this attribute.
reference_attribute NVARCHAR 200 true Internal name of the attribute that references the marketing object.
attribute_name NVARCHAR 200 true Name of the marketing object attribute referenced by this attribute.
is_auto_create NVARCHAR 1 true Whether to create an empty marketing object when users create an object by using a template that contains this attribute. The following values are valid.
  • Y = Yes
  • N = No
is_modifies NVARCHAR 1 true Whether the form updates the marketing object. The following values are valid.
  • Y = Yes, modifies
  • N = No
onClickGoTo INT32 true Tab to display when user clicks on the object link in the form. The following values are valid.
  • 0 = N/A (Used in form attributes)
  • 1 = Summary tab
  • 2 = Analysis tab

uap_tt_tvc

This table stores information about each grid. This table was added in version 8.0.

Field Type Length Null? Description
tvc_id INT32 false Unique identifier generated by the system for each grid.
map_id INT32 false Unique identifier for the form that contains the grid. This column is the foreign key to uap_tt_map.
map_info_group_id INT32 true Unique identifier for the attribute group that contains the grid. This column is the foreign key to uap_tt_map_info_group.
element_name NVARCHAR 100 true Internal name of the grid.
label NVARCHAR 1024 true Display name of the grid.
tablename NVARCHAR 256 true Name of the database table for the grid.
key_column NVARCHAR 256 true Key column of the database table for the grid.
key_type NVARCHAR 50 true Data type of the key column.
viewtype INT32 true The type of grid. The following values are valid.
  • 0 = Line wrap view
  • 1 = Line truncate view
  • 2 = Two line staggered view
  • 3 = Editable grid view
pagesize INT32 true Number of rows per page on the grid user interface.
tab_order INT32 true Tab order of the grid.
parent_filter_column_name NVARCHAR 256 true Name of the column that contains the value on which to filter.
showExportLink NVARCHAR 1 true Indicates whether to show the Export link. The following values are valid.
  • Y = Yes, show
  • N = No
showGroupByLink NVARCHAR 1 true Indicates whether to show the Group by link. The following values are valid.
  • Y = Yes, show
  • N = No
showViewLink NVARCHAR 1 true Indicates whether to show the View link. The following values are valid.
  • Y = Yes, show
  • N = No
defaultSortColumn NVARCHAR 256 true Name of the default sort column for the grid.
dataposturl NVARCHAR 1024 true URL to which to post the data.
is_tcs INT32 true Whether or not the grid is a TCS. The following values are valid.
  • 0 = Not a TCS grid
  • 1 = TCS grid

uap_tt_tvc_map_info

This table stores additional information about attributes used in grids. This table was added in version 8.0.

Field Type Length Null? Description
tvc_map_info_id INT32 false Unique identifier generated by the system for each row in the table.
map_id INT32 false Unique identifier for the form that contains the grid attribute. This column is the foreign key from uap_tt_map.
tvc_id INT32 false Unique identifier for the grid that contains the attribute. This column is the foreign key from uap_tt_tvc.
map_info_id INT32 false Unique identifier for the attribute. This column is the foreign key from uap_tt_map_info.
alignment INT32 true Alignment for the attribute. The following values are valid.
  • left
  • right
  • center
sortType INT32 true How the attribute values are sorted. The following values are valid.
  • ascending
  • descending
is_sortable NVARCHAR 1 true Whether or not the attribute is sortable. The following values are valid.
  • Y = Yes, sortable
  • N = No
can_group_by NVARCHAR 1 true Whether or not the grid can be grouped by this attribute. The following values are valid.
  • Y = Yes, the grid can group by this attribute.
  • N = No
cacheLookupValues NVARCHAR 1 true Indicates whether or not to cache the lookup values for this attribute. The following values are valid.
  • Y = Yes, cache values
  • N = No
objectType INT32 true This column is not used currently.
templateName NVARCHAR 100 true Name of the marketing object template associated with this attribute.
marketingObjectType NVARCHAR 100 true Object type of the marketing objects associated with this attribute.
formatingClass NVARCHAR 1024 true
onClickGoTo INT32 true The tab of the referenced marketing object that opens when users click the object link created by this attribute.
attributeName NVARCHAR 100 true Name of the marketing object attribute this grid attribute references.
objectIdColumnName NVARCHAR 256 true Name of the object instance ID column for the marketing object this attribute references.
objectTypeColumnName NVARCHAR 256 true Name of the object type column for the marketing object this attribute references.
gridColumnName NVARCHAR 256 true Marketing object form grid column name.
summary_function INT32 true Added to support the summary function in grids in version 8.1.0.

uap_tt_map_info_group

This table stores information about attribute groups. This table was added in version 8.0.

Field Type Length Null? Description
map_info_group_id INT32 false Unique identifier generated by the system for each attribute group.
map_id INT32 false ID of the form that contains the attribute group. This column is the foreign key from uap_tt_map.
element_name NVARCHAR 100 true Internal name of the group.
tab_order INT32 Order in which the group appears on the form.
header NVARCHAR 200 true Display name of the group.
description NVARCHAR 1024 true Description of the group.
column_span INT32 Indicates whether the group has a one-column layout or a two-column layout. This column only applies to form attributes. The following values are valid.
  • 1 = One-column
  • 2 = Two-column
show_group NVARCHAR 20 true Indicates whether to show or hide the group heading on the form. The following values are valid.
  • 0 = Hide
  • 1 = Show

uap_tt_tvc_info_group

This table stores information about attribute groups placed on grids. This table was added in version 8.0.

Field Type Length Null? Description
tvc_info_group_id INT32 false Unique identifier generated by the system for each row in the table.
map_id INT32 false Unique identifier for the form that contains the grid that contains the attribute group. This column is the foreign key to uap_tt_map.
tvc_id INT32 false Unique identifier for the grid that contains the attribute group. This column is the foreign key to uap_tt_tvc.
element_name NVARCHAR 100 true Internal name of the attribute group.
tab_order INT32 Tab order of the group.
header NVARCHAR 200 true Display name of the group.
description NVARCHAR 1024 true Description of the group.
show_group NVARCHAR 20 true Indicates whether to show or hide the group name. The following values are valid:
  • 0 = Hide
  • 1 = Show

uap_tt_map_info_option

This table stores the possible values for single-select attributes. This table was added in version 8.0.

Field Type Length Null? Description
map_info_option_id INT32 false Unique identifier generated by the system for each row in the table.
map_id INT32 false ID of the form that contains the single-select attribute. This column is theoreign key to uap_tt_map.
map_info_id INT32 false Unique identifier for the single-select attribute. This column is theoreign key to uap_tt_map_info.
option_value NVARCHAR 1024 true Option value. The size of this column increased in version 8.6.0.
option_display NVARCHAR 1024 true Option display. The size of this column increased in version 8.6.0.
option_order INT32 false Location in which this option appears in the list of options.

uap_tt_map_info_dependent

This table stores information about dependent fields. This table was added in version 8.0.

Field Type Length Null? Description
map_info_dependent_id INT32 false Unique identifier generated by the system for each row in the table.
map_id INT32 false Unique identifier for the form that contains the attribute. This column is the foreign key to uap_tt_map.
map_info_id INT32 false Unique identifier for the attribute. This column is the foreign key to uap_tt_map_info.
map_info_dependent NVARCHAR 50 true Internal name of the child attribute.

uap_tt_ms_link

This table stores information about the junction tables used by multi-select database attributes. This table was added in version 8.0.

Field Type Length Null? Description
ms_link_id INT32 false Unique identifier generated by the system for each row in the table.
map_id INT32 false ID of the form that contains the multi-select database attribute. This column is the foreign key to uap_tt_map.
map_info_id INT32 false ID of the multi-select database attribute. This column is the foreign key to uap_tt_map_info.
table_name NVARCHAR 256 true Name of the junction table.
key_column NVARCHAR 256 true Key column name.
key_type NVARCHAR 50 true Data type for the key column. Unica Plan retrieves the data type from the database.
link_key_column NVARCHAR 256 true Link database key column.
link_key_type NVARCHAR 50 true Data type for ms link key column. Unica Plan retrieves the data type from the database.
root_element NVARCHAR 100 true Name of the multi-select attribute.

uap_tt_localized_text

This table stores display values for all form elements, for example, form groups, grids, and attributes, for all supported locales. This table was added in version 8.0.0.

Field Type Length Null? Description
text_id INT32 false Unique identifier that is generated by the system for each row in the table.
text_key VARCHAR 300 false Internationalization text key.
locale VARCHAR 10 false A supported locale. The following values are valid. Version 8.5 and 9.0 added languages.
  • de_DE
  • en_GB
  • en_US
  • es_ES
  • fr_FR
  • it_IT
  • ja_JP
  • ko_KR
  • pt_BR
  • ru_RU
  • zh_CN
localized_text NVARCHAR 1536 true Display text for this locale.

uap_tt_map_text

This table associates localized display text for form elements with specific forms. This table was added in version 8.0.

Field Type Length Null? Description
map_id INT32 false ID of a form. This column is the foreign key to uap_tt_map.
text_id INT32 false ID of an item of localized text used on the form. This column is the foreign key to uap_tt_localized_text.

uap_tt_std_attr_behavior

This table stores the mapping of the behavior of the attribute with the template. Attributes can be standard, required, or hidden. This table was added in version 9.1.0.

Field Type Length Null? Description
element_name NVARCHAR 50 false The internal name of the attribute.
template_id NVARCHAR 50 false The reference of template id from uap_tt_templates.
behavior INT32 true The behavior of the attribute: standard, required, or hidden.

uap_dual

Field Type Length Null? Description
dummy VARCHAR 1 true

umcm_folder_users

Field Type Length Null? Description
folder_id INT32 false
mem_type_id INT32 false
user_id INT32 false

umcm_item_files

Field Type Length Null? Description
item_id INT32 false
item_version_seq INT32 false
file_id INT32 false

umcm_content_file

Field Type Length Null? Description
file_id INT32 false
file_type INT32 true
created_datetime DATETIME true
mime_type VARCHAR 255 true
file_name NVARCHAR 255 true
file_size VARCHAR 20 true

umcm_item_hist

Field Type Length Null? Description
item_id INT32 false
seq_no INT32 false
item_version_seq INT32 true
prev_status VARCHAR 20 true
curr_status VARCHAR 20 true
comments NVARCHAR 1024 true
last_mod_date DATETIME true
user_id INT32 true

uap_event_cat_enum

This table contains details about event categories.

Field Type Length Null? Description
cat_value INT32 false
cat_name VARCHAR 50 true
cat_desc NVARCHAR 100 true

uap_object_enum

Field Type Length Null? Description
obj_value INT32 false
obj_name VARCHAR 50 true
obj_desc NVARCHAR 100 true

uap_mem_notify

Field Type Length Null? Description
mem_type_id INT32 false
delivery_mask INT32 true
event_type INT32 false

uap_notify_msg

Field Type Length Null? Description
event_type INT32 false Type of the event.
object_type INT32 true Object type of the event.
category_type INT32 true Category of the event.
description NVARCHAR 200 true Message description.
display_order INT32 true Display order of event.
is_exclusive INT32 true Indicates whether this event support personalization. This column was added in version 7.5.

uap_appr_notify

Field Type Length Null? Description
event_type INT32 false
user_id INT32 false
delivery_mask INT32 true
approval_id INT32 false ID of an approval item. This column is the foreign key to uap_approvals.

uap_appr_msgboard

Field Type Length Null? Description
approval_id INT32 false ID of an approval item. This column is the foreign key to uap_approvals.
msg_id INT32 false Unique ID generated by the system for each message on the approval message board.
post_datetime DATETIME false Date the message was posted on the approval message board.
user_id INT32 false Unique ID of the user who posted the message. This column is the foreign key to uap_user.
msg_text NVARCHAR 1024 true Text of the message.
recipients NVARCHAR 512 true List of recipients if the message is emailed.

uap_appr_users

This table contains information about the users who are participating in an approval process.

Field Type Length Null? Description
approval_id INT32 false Unique ID generated by the system for each approval item that is created by a user. This column is the foreign key to uap_approvals.approval_id.
user_id INT32 false User ID of the user. This column is the foreign key to uap_users.user_id.
mem_type_id INT32 false Member type ID of the user in this approval process. This column is the foreign key to uap_member_type.mem_type_id.

uap_approval_hist

This table contains data that tracks the history of approval events and actions.

Field Type Length Null? Description
approval_id INT32 false Unique ID generated by the system for each approval item that is created by a user. This column is the foreign key to uap_approvals.approval_id.
seq_no INT32 false Sequence (order) of the historical event.
cur_state VARCHAR 20 true Current state of the approval process after the user completes the action/event. The following values are valid.
  • WAITING
  • APPROVED
  • APPR_WITH_CHANGES
  • DENIED
comments CLOB true Comments that the user made at the time of the action or event.
prev_state VARCHAR 20 true Previous state of the approval process before the user initiated the action or event.The following values are valid.
  • WAITING
  • APPROVED
  • APPR_WITH_CHANGES
  • DENIED
last_mod_date DATETIME true Time that the modification to the approval occurred.
user_id INT32 true User ID of the user who initiated the action/event to the approval. This column is the foreign key to uap_users.user_id.
on_behalf_user_id INT32 true This column was added in version 7.4.0.
appr_item_seq INT32 true appr_item_seq: From uap_appr_items. This column stores the approval item id. It is only updated when an approver responds to an individual item. The comment is stored as: "Approval Item approved / denied / approved w/ changes". If the row represents the approval item response, then cur_state and prev_state represents the state of the approval item and not of the overall approval. comment_file_id: Points to the comment attachments file for the item that is referred to by appr_item_seq. This column is populated only when an approver attaches a comments file in response to the approval item.This column is the primary key from uap_appr_items.
appr_item_resp_id INT32 true Points to the item response row for the item that is referred to by appr_item_seq. This column is the foreign key from uap_appr_item_response. The row in the history table points to the original item response tables row.
round_no INT32 true When approval is added and started the first time, the round number is 1 and the resubmit count is 0. Each time the approval is resubmitted, the round number increments and the next round of approval starts. The round_no = theresubmit count + 1. In this case, 1 is added to consider first round of approval.
detailed_history CLOB true This column is updated when there is any change in the reviewer steps. This column is introduced as Clob so that detailed history can be stored in this column later on.
deny_reason_id INT32 true Identifies the deny reason that is selected when a user denies an approval. This column is the foreign key to uap_appr_deny_reasons. deny_reason_id. This column was added in version 9.0.

uap_group_users

Field Type Length Null? Description
group_id INT32 false
user_id INT32 false

uap_groups

Field Type Length Null? Description
group_id INT32 false
group_name NVARCHAR 50 true
group_desc NVARCHAR 255 true
asm_group_id INT32 true
parent_group INT32 true

umcm_item_users

Field Type Length Null? Description
item_id INT32 false
item_version_seq INT32 false
user_id INT32 false This column is the foreign key to uap_users.user_id
mem_type_id INT32 false

umcm_item_usage

Field Type Length Null? Description
item_id INT32 false
item_version_seq INT32 false
affinium_system NVARCHAR 100 true
est_usage_qty INT32 true

umcm_item_content

Field Type Length Null? Description
item_id INT32 false
item_version_seq INT32 false
version_label NVARCHAR 50 true
item_keywords NVARCHAR 1024 true The string length was changed from 255 characters to 1024 characters in version 9.0.0.
version_comments NVARCHAR 1024 true
last_mod_user_id INT32 true
last_mod_datetime DATETIME true
item_published VARCHAR 1 true
expiration_date DATETIME true
version_name NVARCHAR 255 true
version_status VARCHAR 20 true
item_instructions NVARCHAR 1024 true
created_user_id INT32 true
create_datetime DATETIME true
item_desc NVARCHAR 1024 true

umcm_items

Field Type Length Null? Description
item_id INT32 false
folder_id INT32 true
item_name NVARCHAR 256 true The size of this column increased to 256 in version 7.4.0.
item_versions INT32 true
last_mod_datetime DATETIME true
parent_folder_path NVARCHAR 1024 true
item_cr_datetime DATETIME true
item_code NVARCHAR 20 true
asset_legacy_ind INT32 false This column was added in version 8.1.0 to support identification of legacy and current assets.

umcm_folders

Field Type Length Null? Description
folder_id INT32 false
folder_name NVARCHAR 255 true
folder_display NVARCHAR 255 true
folder_desc NVARCHAR 1024 true
parent_folder_id INT32 true
folder_status NVARCHAR 20 true
parent_folder_path NVARCHAR 1024 true
sec_policy_id INT32 true
folder_type VARCHAR 20 true
folder_location NVARCHAR 1024 true

uap_user_roles

This table maps a user to a security role in a security policy. A user has a row in this table for each security policy in which that user participates.

Field Type Length Null? Description
role_id INT32 false This column is the foreign key into uap_roles.role_id.
sec_policy_id INT32 false This column is the foreign key into uap_sec_policy.sec_policy_id.
user_id INT32 false This column is the foreign key into uap_user.user_id.

uap_role_fn_map

Field Type Length Null? Description
role_id INT32 false
function_id INT32 false
extd_perm VARCHAR 1 true

uap_role_user_vis

This table supports limited resource selection. It stores user visibilities for security roles defined in Unica Plan administration. This table was added in version 7.4.1.

Field Type Length Null? Description
role_id INT32 false
group_id INT32 false
team_id INT32 false

uap_roles

Field Type Length Null? Description
role_id INT32 false
role_name NVARCHAR 50 true The name of the role.
role_desc NVARCHAR 255 true The text description of the role.
sec_policy_id INT32 true This column is the foreign key into uap_sec_policy.sec_policy_id.

uap_mem_fn_map

Field Type Length Null? Description
mem_type_id INT32 false
function_id INT32 false
sec_policy_id INT32 false

uap_sec_policy

Field Type Length Null? Description
sec_policy_id INT32 false Unique identifier for the security policy.
name NVARCHAR 50 true Name for the security policy as it appears in Unica Plan
description NVARCHAR 255 true Text description for the security policy.
policy_type VARCHAR 20 true Security policy type. The following values are valid.
  • S = Global policy
  • C = all other policies
status_code VARCHAR 20 true Status of the policy. The following values are valid.
  • ACTIVE
  • DISABLED

uap_function

This table contains entries for all the functions in the system; each function can be granted, denied, or inherited for a particular security policy. This table was added in version 7.3.

Field Type Length Null? Description
function_id INT32 false Unique identifier for the function.
name VARCHAR 50 true Internal name of the function.
description NVARCHAR 150 true Display name for the function.
object_fn_cat VARCHAR 50 true Category of the function. The following values are valid.
  • ACCESSIBLE_OPTION
  • ANALYSIS
  • ATTACHMENT
  • BUDGET
  • FOLDERS
  • GENERAL
  • PEOPLE
  • SUMMARY
  • TRACKING
  • WORKFLOW
object_type VARCHAR 50 true Type of object (for example, project) that uses this function.
display_order INT32 true The order in which to display this function on screen when setting permissions.

uap_approver_list

This table stores the lists of approvers (reviewers) of an approval process and information about their instructions and responsibilities.

Field Type Length Null? Description
approval_id INT32 false Unique ID generated by the system for each approval item that is created by a user. This column is the foreign key to uap_approvals.approval_id.
approver_seq INT32 false Sequence of the approval (identifier).
status_code VARCHAR 20 true Identifies the overall approval status. For example, if an approver completes the response by clicking Post Complete Response, the status of the approval is updated here.
appr_order INT32 true Display order of users.
user_id INT32 true User ID of the user. This column is the foreign key to uap_users.user_id.
required VARCHAR 1 true Whether the approver is required. The following values are valid.
  • Y = Yes, required
  • N = No
instructions NVARCHAR 1024 true Instructions to an approver.
resp_date DATETIME true The date on which the complete response is posted.
team_id INT32 true Team ID: value of -1 indicates that the approver for this item is not a team. This column was added in version 7.4.0.
assignment_type INT32 true
rule_id INT32 true This column was added in version 7.5. It points to the rule if assignment type is rule driven. This column is used only for workflow approval tasks. For old approvals, this column is blank (null). This column is the primary key from uap_tt_wf_rule.rule_id.
duration VARCHAR 20 true Duration for each review step is stored in this column. For old approvals, the upgrade is done by planUpgrade.jsp as follows. First, the system counts the number of days between the target due date and the creation or start date. If the approval method is simultaneous, the duration equals the number of days. If the approval method is sequential, then the duration is the number of days divided by the number of approvers. This column was added in version 7.5.
role_id INT32 true This column stores the reviewer role that is specified in review step. This column is used only for workflow approval tasks. This column is the primary key from uap_roles.role_id. Earlier roles were stored in uap_appr_usr_role and uap_appr_team_role tables. During the upgrade to version 7.5 (planUpgrade.jsp), these values were copied into this new column.Note that uap_appr_usr_role continues to store owner roles. It does not store reviewer roles from 7.5 onwards. Only reviewer roles are migrated to role_id.
deny_reason_id INT32 true Identifies the deny reason that is selected when a user denies an approval. This column is the foreign key to uap_appr_deny_reasons. deny_reason_id. This column was added in version 9.0.

uap_appr_item_hist

Field Type Length Null? Description
appr_item_seq INT32 false
curr_item_seq INT32 false
comments NVARCHAR 1024 true
approval_id INT32 false
user_id INT32 true

uap_appr_items

This table contains information for individual approval items in an approval process.

Field Type Length Null? Description
appr_item_seq INT32 false Sequence of the item (identifier).
create_date DATETIME true Date that the item was created.
notes NVARCHAR 1024 true Notes about the item.
item_status VARCHAR 20 true Status of the item under review, specifically whether or not it is archived. Valid values are ARCHIVED or null.
user_id INT32 true User who added the item to the approval process. This column is the foreign key to uap_users.
orig_file_name NVARCHAR 390 true Original file name of the uploaded file. The size of this column changed to 256 in version 7.4.0. The size changed to 390 in version 9.1.0.
approval_id INT32 false ID of an approval item. This column is the foreign key to uap_approvals.
markup_file_name NVARCHAR 256 true Markup file name.
orig_file_size VARCHAR 10 true Size of the original file.
orig_file_mime VARCHAR 255 true MIME type of the original file. The size increased in version 8.6.0.
last_mod_date DATETIME true Date the item was last modified.
enable INT32 true
linked_item_id INT32 true The ID of the line item to which the approval item belongs. This column was added in version 8.5.0.
object_id INT32 true For marketing objects, the ID of the object. Value is -1 for other objects. This column is the foreign key to uap_mktgobject.mktg_object_id. This column was added in version 7.3.0.
map_name NVARCHAR 50 true
object_type_id INT32 true For marketing objects, the ID of the marketing object type. Value is -1 for other objects. This column is the foreign key into uap_comp_type.comp_type_id. This column was added in version 7.3.0.
flowchart_id INT32 true ID of the flowchart if this row is for flowchart approval. This column was added in version 12.0.
object_subtype_id INT Stores the subtype of offer, or offer list, attached to the approval through Centralized Offer Management's offer picker widget.
  • 0 - Offer
  • 1 - Static offer list
  • 2 - Smart offer list

uap_approvals

The uap_approvals table is the main table for storing information about the approval processes that are created by users.

Field Type Length Null? Description
approval_id INT32 false Unique ID generated by the system for each approval item that is created by a user.
name NVARCHAR 256 true Name of the approval process. The size of the column changed to 256 in version 7.4.0.
description NVARCHAR 1024 true Description of the approval.
submit_date DATETIME true The date the approval was submitted.
created_by INT32 true User ID of the user who created this approval.
state_code VARCHAR 20 true State of the approval. The following values are valid.
  • NOT_STARTED
  • IN_PROGRESS
  • ON_HOLD
  • COMPLETED
  • CANCELLED
create_date DATETIME true Date that the approval was created.
src_object_id INT32 true Project ID of the project that the approval was created in.
approval_date DATETIME true Approved date.
src_wf_step INT32 true Workflow integer (ID) of the approval that the approval was linked to.
sec_policy_id INT32 true Security policy ID of the security policy for this approval.
status_code VARCHAR 20 true Status of the approval. The following values are valid.
  • PENDING
  • ACTIVE
  • FINISHED
  • SKIPPED
last_mod_date DATETIME true Last modified date.
completed_date DATETIME true Approval completed date.
src_object VARCHAR 20 true Source object type. Value: PROJECT.
method INT32 true Method of the approval.
  • 0 = Simultaneous, all approvers approve at the same time
  • 1 = Sequential, approvers approve in sequence
resubmit_count INT32 true the number of times this approval has been resubmitted.
disposition_all_docs VARCHAR 2 true
  • Y = The approval owner requires all documents to be dispositioned by the reviewer before the review can be marked complete.
  • N = Dispositioning a single document is permitted.
This column was added in version 7.5.
auto_complete VARCHAR 2 true
  • Y = Auto-completion is allowed
  • N = Auto-completion not allowed
reapproval_rule INT32 true The following values are valid.
  • 101 = If the approval is rejected and then resubmitted, the approval is processed by all approvers.
  • 102 = If the approval is rejected and then resubmitted, start the approval process with the person who rejected it.
  • 103 = If the approval is edited or resubmitted, the approval owner selects the approvers who must reapprove. For stand-alone approvals, this value is 103 by default.
Note that although 103 is stored for stand-alone approvals, in the user interface for resubmit all approvers display and the owner can select or clear the approvers. This column is set to 103 for all older (pre- 7.3) approvals.This column was added in version 7.3.
enable_commts_attchmnt VARCHAR 2 true Indicates whether the approvers can add attachments when they respond to the approval. This column is set to Y for stand-alone approvals by default. This column is set to N for approvals made in versions before 7.5.This column was added in version 7.5.The following values are valid.
  • Y = Yes, enable markup
  • N = No
enable_markup VARCHAR 2 true For workflow approvals, this column stores the value of the enable markup flag that is defined by the project workflow settings for approvals. For stand-alone approvals, this column stores Y or N based on the system-wide enable markup setting.This column was added in version 7.5. For approvals made in versions before 7.5, this column contains the system-wide enable markup setting made by planUpgrade.jsp. The following values are valid.
  • Y = Yes, enable markup
  • N = No
add_rem_approvers VARCHAR 2 true
approval_start_date DATETIME true
approval_task_viewed VARCHAR 2 true
approval_type INT32 true This column identifies budget and non-budget approvals. The following are valid values.
  • 1 = non-budget approvals
  • 2 = budget approvals
This column was added in version 8.5.0.
approve_with_changes VARCHAR 1 true Default value is 1. This column was added in version 10.0.0.2.
version INT32 false Required for optimistic locking to manage the synchronization of concurrent updates to the table.

uap_appr_item_response

This table stores the current response status of each item in an approval from each approver. This table was added in version 7.5.

Field Type Length Null? Description
appr_item_resp_id INT32 false Approval item response.
approval_id INT32 false Parent approval. This column is the primary key from uap_approval.
approval_item_seq INT32 false Approval item. This column is the primary key from uap_appr_items. appr_item_seq.
user_id INT32 false Approver user ID. This column is the primary key from uap_user.user_id.
status_code NVARCHAR 20 true Response status for current approval item. The following values are valid.
  • null or blank
  • approve
  • approve with changes
  • deny
resp_comments NVARCHAR 1024 true Stores the comments or feedback that is provided by an approver in response to the approval item.
resp_date DATETIME true Response date.
deny_reason_id INT32 true Identifies the deny reason that is selected when a user denies an approval. This column is the foreign key to uap_appr_deny_reasons. deny_reason_id. This column was added in version 9.0.

uap_appr_commt_attchment

This new master table for comment attachment files was added in version 7.5.

Field Type Length Null? Description
approval_id INT32 false Approval in response to whom this comment attachment was added. This column is the primary key from uap_approvals.approval_id.
seq_no INT32 true Sequence number of the comment.
appr_item_resp_id INT32 true Approval item response id. This column is the primary key from uap_appr_item_response. appr_item_resp_id.
approval_item_seq INT32 true Approval item in response to whom this comment attachment was added. This column is the primary key from uap_appr_items.
comment_file_id INT32 false Comment file ID.
user_id INT32 false Approver user ID who uploaded this comment file. This column is the primary key from uap_user.user_id.
create_date DATETIME false Date of creation.
orig_file_name NVARCHAR 1024 false Original file name.
orig_file_size INT32 true Attachment file size.
orig_file_mime VARCHAR 255 true Attachment file mime type. The size was increased in version 8.6.0.

uap_user_list

Field Type Length Null? Description
column_type VARCHAR 20 false Possible values are list or calendar.
seq_no INT32 false List column sequence number.
column_id VARCHAR 255 true List column database column ID/name.
flag_ext_col VARCHAR 1 true The following values are valid.
  • Y = if this column is extended/custom database table column.
  • N = if this column is a standard/predefined column
ufolder_id INT32 false Unique ID generated by the system for each folder that is personalized by a user.
tab_order INT32 true List column display order.

uap_genids

Field Type Length Null? Description
entity_name NVARCHAR 50 false
entity_key NVARCHAR 50 false
key_value INT32 true

uap_email_alerts

Field Type Length Null? Description
notify_id INT32 false
user_id INT32 false
resend_count INT32 false
resend_datetime DATETIME true
resend_status INT32 false
source_type INT32 false
source_id INT32 false
message NVARCHAR 1024 true

uap_ws_alerts

Field Type Length Null? Description
notify_id INT32 false
user_id INT32 false
post_datetime DATETIME true
status INT32 true
source_type INT32 true
source_id INT32 true
message NVARCHAR 1024 true

uap_proj_notify

Field Type Length Null? Description
event_type INT32 false
project_id INT32 false Unique ID generated by the system for each project and project request. This column is the foreign key to uap_projects.
user_id INT32 false
delivery_mask INT32 false

uap_user_folders

Field Type Length Null? Description
user_id INT32 false Folder user ID. This column is the foreign key to uap_user.user_id.
ufolder_id INT32 false Unique ID generated by the system for each folder that is personalized by a user.
folder_type VARCHAR 50 true Folder type. The following values are valid.
  • PROJECT
  • PROGRAM
  • PLAN
name NVARCHAR 100 true Folder name.
description NVARCHAR 255 true Folder description.
parent_folder INT32 true Parent system folder ID. This value refers to the ufolder_id of another row within this table.
sys_folder_id INT32 true System folder ID. The following values are valid.
  • -1 = User saved search
  • 1 = All Projects and Requests
  • 2 = All Active Projects and Requests
  • 3 = My Projects
  • 4 = My Requests
  • 11 = All Programs
  • 12 = Active Programs
  • 13 = My Programs
  • 21 = All Invoices
  • 22 = My Active Invoices
  • 31 = All Plans
  • 32 = Active Plans
  • 41 = All Accounts
  • 42 = Active Accounts
  • 51 = All RFQs
  • 52 = Active RFQs
created_by INT32 true User ID of the user who created the search. This column is the foreign key to uap_user.user_id.
sort_by NVARCHAR 255 true
sort_order VARCHAR 4 true

uap_user_pref

This table stores the customizations and preferences selected by users. For example, each user can select a home or starting page to display on login.

Field Type Length Null? Description
user_id INT32 false User identifier. This column is the foreign key into uap_user.
ufolder_id INT32 true
default_module VARCHAR 20 true The module that this user sees after logging in.; for example, Approvals.
default_menu_group VARCHAR 64 true Defines the left menu group that displays for each user after logging in. This column was added in version 7.3.
default_view VARCHAR 20 true Default view for the module: list or calendar.
proj_wf_view VARCHAR 20 true
default_cal_opt VARCHAR 1 true
default_alert_ch VARCHAR 20 true
default_sec_policy INT32 true ID for the default security policy for this user.
appr_folder_id INT32 true The approval folder for the user.
hlth_mail_sbscrptn INT32 true Indicates whether the user subscribes to daily notifications by email for project health status calculations.

uap_user_object_prefs

This table stores the user preferences of a new workflow for a given project. It allows different users to see different columns, in the grid, for a new workflow.

Field Type Length Null? Description
user_id INT false User identifier. This is part of the primary key. The other parts include "object_type" and "object_id".
object_type VARCHAR 256 false The type of marketing object. For example, project_wf (project workflow). This is part of the primary key. The other parts include "user_id" and "object_id".
object_id INT false The ID of the marketing object. For example, project ID. This is part of the primary key. The other parts include "user_id" and "object_type".
pref CLOB JSON string that stores the actual preference value.

An example for the pref column is as follows:

{"columns":[\{"name":"status","visible":true},
{"name":"percentCompletion","visible":true},
{"name":"fcStartDate","visible":true},
{"name":"fcEndDate","visible":true},
{"name":"fcDurationStr","visible":true},
{"name":"actualEffortStr","visible":true},
{"name":"anchor","visible":true},
{"name":"schedule","visible":true},
{"name":"targetStartDate","visible":true},
{"name":"targetEndDate","visible":true},
{"name":"targetDurationStr","visible":true},
{"name":"targetEffortStr","visible":true},
{"name":"mileStone","visible":true},
{"name":"memberRolesAndRoleTypes","visible":true},
{"name":"members","visible":true},
{"name":"reviewerroles","visible":true},
{"name":"reviewers","visible":true},
{"name":"enforceDependency","visible":true},
{"name":"required","visible":true},
{"name":"taskCode","visible":true},
{"name":"notes","visible":true}]}

uap_user_menu_pref

This table specifies, for each user, the menu and menu item to display as the default when the user logs in. This table was added in version 7.3.

Field Type Length Null? Description
user_id INT32 false Unique ID generated by the system for each user.
menu_group_id VARCHAR 64 false User starts with this menu group upon logging in.
default_menu_item VARCHAR 64 false User sees this menu item as the default upon logging in.

uap_utype_pref

Each user can set their starting screen when they view projects. This table stores those preferences. This table was new in version 7.3.

Field Type Length Null? Description
user_id INT32 false User ID. This column is the foreign key to uap_users.user_id.
type VARCHAR 50 false Type of object to start in (for example, project).
startup_folder_id INT32 true ID of the folder to start in. For example, 1 corresponds to All Projects and Requests. This column is the foreign key into uap_user_folders.ufolder.id.
default_view VARCHAR 20 true The default view for the Task view: List or Calendar. This column was added in version 7.4.1.
startup_report NVARCHAR 1024 true

uap_wf_users

Field Type Length Null? Description
wf_no INT32 false Unique ID generated by the system for each workflow step or stage.
user_id INT32 false User ID of a workflow task member. This column is the foreign key to uap_user.
est_usage INT32 true
act_usage INT32 true

uap_proj_users

This table contains the basic data for all project users.

Field Type Length Null? Description
project_id INT32 false
user_id INT32 false Project team member internal user ID.
mem_type_id INT32 false Project member type role ID. This column is the foreign key to uap_member_type. The following values are valid.
  • Owner
  • Requestor
  • Participant
  • Participant
team_id INT32 true Team ID. This column was added in version 7.4.0.
step_id INT32 false

uap_user

This table stores information about users defined in the system. It contains user definitions and privileges.

Field Type Length Null? Description
user_id INT32 false
last_name NVARCHAR 80 true The size of the column was increased to 80 in version 8.0.
first_name NVARCHAR 64 true The size of the column was increased to 64 in version 8.0.
phone_off_id NVARCHAR 20 true Office phone number of the user. This column was added in version 12.0.
phone_mob_id NVARCHAR 20 true Mobile phone number of the user. This column was added in version 12.0.
phone_home_id NVARCHAR 20 true Home phone number of the user. This column was added in version 12.0.
last_mod_date DATETIME true
login_id NVARCHAR 200 true The size of the column was changed from 20 to 100 in version 7.3. The size of the column was changed from 100 to 200 in version 8.0.
email_id NVARCHAR 128 true The size of the column was changed from 100 to 128 in version 12.1.
status_code VARCHAR 20 true The following values are valid.
  • ENABLED
  • DISABLED
  • DELETED
locale VARCHAR 10 true The locale for the user. This column was added in version 7.4.0.
timezone VARCHAR 30 true This column stores the timezone of the user. This column was added in version 9.1.0.

uap_metric_grps

This table stores details about metric groups.

Field Type Length Null? Description
project_id INT32 false Unique ID that is generated by the system for each project and project request.
grp_id VARCHAR 50 false ID of the metric group.
grp_display NVARCHAR 100 true Display name for the metric group.
grp_display_order INT32 true Display order of the metric group.

uap_metric_cols

Field Type Length Null? Description
project_id INT32 false
col_taborder INT32 false Metrics display order.
col_display NVARCHAR 100 true Metric display name.
flag_target VARCHAR 1 true Flag whether this metric is a target.
dimension_type VARCHAR 20 true Metric dimension type.

uap_proj_msgboard

Field Type Length Null? Description
project_id INT32 false Unique ID that is generated by the system for each project and project request. This column is the foreign key to uap_projects.
msg_id INT32 false Unique ID that is generated by the system for each message on the project message board.
post_datetime DATETIME false Date the message was posted on the project message board.
user_id INT32 false Unique ID of the user who posted the message. This column is the foreign key to uap_user.
msg_text NVARCHAR 1024 true Message text.
recipients NVARCHAR 512 true List of recipients if the message is emailed.
collab_message_id VARGRAPHIC 128 Generated by third-party collaboration service. For example, Slack.
collab_message_status INT Status of the message as to whether it is posted on collaboration channel or not.
  • -1 - Not posted
  • 1 - Posted

uap_proj_metrics

This table holds metrics for the project, both user-entered and computed by the system.

Field Type Length Null? Description
metric_id NVARCHAR 50 false Metric ID name.
display_name NVARCHAR 100 true Metric display name.
display_order INT32 true Metric display tab order
units VARCHAR 20 true Metric unit type.
input_method VARCHAR 20 true Metric value input method. The following values are valid.
  • USER
  • COMPUTE
  • LINK
  • ROLLUP
display_format VARCHAR 20 true Metric value display format.
metric_value1 FLOAT true Metric value 1.
metric_value3 FLOAT true Metric value 3.
metric_value2 FLOAT true Metric value 2.
metric_columns INT32 true Number of metric dimensions or columns. The maximum number of supported dimensions or columns is 5.
description NVARCHAR 1024 true
formula NVARCHAR 1024 true Formula used to compute the metric. The value cannot be null if input_method is COMPUTE. The value is null if input_method is USER.
metric_mprecision INT32 true Metric value decimal precision.
metric_value4 FLOAT true Metric value 4.
grp_id NVARCHAR 50 true Metric group ID. This column is the foreign key to uap_metric_grps.
metric_value5 FLOAT true Metric value 5.
project_id INT32 false Unique ID generated by the system for each project and project request. This column is the foreign key to uap_projects.
metric1_nan NVARCHAR 20 true Metric 1 value internal computation errors/conditions saved for display.
metric2_nan NVARCHAR 20 true Metric 2 value internal computation errors/conditions saved for display.
metric3_nan NVARCHAR 20 true Metric 3 value internal computation errors/conditions saved for display.
metric4_nan NVARCHAR 20 true Metric 4 value internal computation errors/conditions saved for display.
metric5_nan NVARCHAR 20 true Metric 5 value internal computation errors/conditions saved for display.
planned_value FLOAT true Planned metric value.
planned_nan NVARCHAR 20 true Planned metric value internal computation errors/conditions saved for display.
is_planned_value VARCHAR 1 true Indicates whether this metric is a planned value. If this value is Y, then this metric has a planned value. The following values are valid.
  • Y = Yes, planned
  • N = No
show_as_rollup VARCHAR 1 true Indicates whether this metric shows in rollup display table.
is_visible VARCHAR 1 true Flag if this metric is visible in the user interface. If this value is Y, this metric appears in the user interface. Otherwise, it is hidden and used for computation purpose. The following values are valid.
  • Y = Yes, visable
  • N = No

uap_proj_hist

This table contains details about the project history.

Field Type Length Null? Description
seq_no INT32 false Unique number generated by the system for project revision history.
user_id INT32 true ID of the user who modified the project.
on_behalf_user_id INT32 true
last_mod_date DATETIME true Date on which the project was last modified.
status_code VARCHAR 20 true Project status
project_id INT32 false Unique ID generated by the system for each project or project request.
cur_state VARCHAR 20 true Current state of the project.
comments CLOB true Comments for recipients. The data type was changed to CLOB in version 9.0.0.
prev_state VARCHAR 20 true Previous state of the project.

uap_proj_attach

Field Type Length Null? Description
attach_id INT32 false Unique ID that is generated by the system for each project attachment.
attach_orig_file NVARCHAR 255 true Original file name of the attachment.
user_id INT32 true
attach_file VARCHAR 255 true
mime_type VARCHAR 255 true
attach_note NVARCHAR 1024 true
attach_size VARCHAR 100 true
attach_date DATETIME true
flag_mcm_item VARCHAR 1 true
item_version_seq INT32 true
item_id INT32 true
proj_folder_id INT32 true
enable INT32 true Defines the markup state for a project attachment for the Markup Anywhere feature. This column was added in version 7.3.

uap_proj_folders

Field Type Length Null? Description
proj_folder_id INT32 false Unique ID that is generated by the system for each project attachments category or folder.
folder_name NVARCHAR 255 true
folder_disp_name NVARCHAR 1024 true
parent_folder INT32 true
project_id INT32 true Unique ID of the project. This column is the foreign key to uap_projects.
proj_folder_type VARCHAR 20 true

uap_wf_dep

Project workflow steps can have one or more dependencies within a project workflow

Field Type Length Null? Description
wf_no INT32 false Unique ID generated by the system for each workflow step or stage. This column is the foreign key to uap_workflow.
dep_seq INT32 false
project_id INT32 true Unique ID generated by the system for each project and project request. This column is the foreign key to uap_projects.
dep_wf_no INT32 true Workflow step internal number/ID.

uap_workflow

This table contains data for project workflow stages and steps.

Field Type Length Null? Description
wf_no INT32 false Unique identifier generated by the system for each workflow step or stage.
name NVARCHAR 256 true Name of the stage or step. The size of this column was changed to 256 in version 7.4.0.
display_order INT32 true
flag_stage VARCHAR 1 true Step display integer. This value appears along with the step or stage name in the user interface. For example, "1.1 Creative Approval Task".
start_date DATETIME true Workflow step start date.
end_date DATETIME true Workflow step end date.
flag_anchor VARCHAR 1 true Anchor date flag.
duration FLOAT true Workflow step predicted or forecasted duration.
actual_duration FLOAT true Workflow step actual duration.
pcnt_comp INT32 true Percent complete.
project_id INT32 true Unique identifier for a project or project request. This column is the foreign key for uap_projects.
flag_link VARCHAR 1 true Workflow step link flag. The value is Y if the step is linked and null if it is not linked.The following values are valid.
  • Y = Yes, linked
  • null
forecast_st DATETIME true Workflow step forecast start date.
link_id INT32 true Workflow step link object ID. If the value for the link_object column is APPROVAL, the value for this column is the approval ID.
parent_stage INT32 true Parent stage if the entry is a step.
status_code VARCHAR 20 true
notes NVARCHAR 1024 true
milestone_type INT32 true Workflow step or task milestone type. This column is the foreign key to uap_wf_milestone.
display_id VARCHAR 10 true
link_object VARCHAR 20 true The type of task For example, peopletask or approval.
state_code VARCHAR 20 true Status of the stage or task. The following values are valid.
  • PENDING
  • ACTIVE
  • FINISHED
  • SKIPPED
forecast_end DATETIME true Workflow step forecast end date.
effort FLOAT true Estimated effort.
actual_effort FLOAT true Actual effort.
task_code NVARCHAR 100 true Used to uniquely identify tasks, for use with the SDK. This column was added in version 7.5.
enable_attachment INT32 true Flag to allow attachments to task. This column was added in version 7.5.
duration_str VARCHAR 20 true Workflow step predicted/forecast duration in DD-HH-MM format. This column was added in version 7.5.
actual_duration_str VARCHAR 20 true Workflow step actual duration of the task in DD-HH-MM format. This column was added in version 7.5.
effort_str VARCHAR 20 true Estimated effort of the task in DD-HH-MM format. This column was added in version 7.5.
actual_effort_str VARCHAR 20 true Actual effort in DD-HH-MM format. This column was added in version 7.5.
required VARCHAR 2 true Workflow task is required task or not. This column was added in version 7.5.
enforce_dep VARCHAR 2 true Flag to enforce dependency between workflow task. This column was added in version 7.5.
scheduling_mask INT32 true Determines whether a workflow step schedules through weekends, non-work time, or both.
ready_to_start INT32 true This column was added in version 8.2.0. It identifies whether dependencies are complete.
  • 0 = Not ready to start
  • 1 = Ready to start
map_name NVARCHAR 50 true
last_mod_date TIMESTAMP true This column was added in version 8.5.0. The last updated date for the workflow task.
rework VARCHAR 10 true Workflow step internal number/ID. This column was added in version 10.0.0.2
rework_count INT32 true Count which represents number of times the task was triggered as part of rework loop. This column was added in version 10.0.0.2
version INT32 false Required for optimistic locking to manage the synchronization of concurrent updates to the table.

uap_projects

This table stores base data for projects and project requests. Data that corresponds to user-defined attributes is stored in the user-defined tables.

Field Type Length Null? Description
project_id INT32 false Unique ID generated by the system for each project and project request.
name NVARCHAR 256 true Name of the project or project request. Version 7.4 increased the size to 256.
description NVARCHAR 1024 true
status_code VARCHAR 20 true Current status of the project. The following values are valid.
  • LATE
  • OVERDUE
start_date DATETIME true Project target start date. Start_date is populated from the Target Start Date on the Summary Tab. There is no other criteria as long as it is populated on the Summary tab.
end_date DATETIME true Project target end date. End_date is populated from the Target End Date on the Summary Tab. There is no other criteria as long as it is populated on the Summary tab.
duration INT32 true
created_by INT32 true ID of the user who created the project or project request.
created_date DATETIME true Date and time the project or project request was created.
modified_by INT32 true ID of the user who last modified the project or project request.
last_mod_date DATETIME true Date when the project or project request was last modified.
pcnt_comp INT32 true
flag_anchor VARCHAR 1 true This column was added in version 9.0.
req_comp_date DATETIME true Project request completion date. This column is null if the project request is not completed or if the entry is a project.
wf_steps INT32 true This column was added in version 9.0.
state_code VARCHAR 20 true Current state of the project or project request. For project requests, the following values are valid.
  • DRAFT
  • SUBMITTED
  • RETURNED
  • ACCEPTED
  • CANCELLED
For projects, the following values are valid.
  • NOT_STARTED
  • IN_PROGRESS
  • ON_HOLD
  • CANCELLED
  • COMPLETED
template_name NVARCHAR 50 true Name of the project template.
metric_template NVARCHAR 50 true Project metric template name. The value for this column is null if the project does not have any metrics.
error_code VARCHAR 20 true This column was added in version 9.0.
actual_st_date DATETIME true Project actual start date. Actual_st_date is based on the state (state_code) of the Project. Actual_st_date is populated with the current datetime the project is set to In Progress.
proj_code NVARCHAR 100 true This column was added in version 9.0.
flag_proj_request VARCHAR 1 Whether it is a project or project request. The following values are valid.
  • Y = project request
  • N or null = project
proj_request_id INT32 true ID of the project request from which this project was created. The value of this column is null if the item is a project request or a project that was not created from a project request.
sec_policy_id INT32 true Project security policy in which the current project is created. The value determines the visibility for the project.
req_submit_date DATETIME true Project request submit date. This column is null until the request is submitted or if the entry is a project.
actual_end_date DATETIME true Project actual end date. Actual_end_date is based on the state (state_code) of the Project. Actual_end_date is updated with the current datetime the project is set to Cancel or Finished.
campaign_id INT64 true This column was added in version 9.0.
mt_mod_date DATETIME true This column was added in version 9.0.
mt_finalized_date DATETIME true This column was added in version 9.0.
mt_import_date DATETIME true This column was added in version 9.0.
parent_proj_id INT32 true
root_proj_id INT32 true
proj_level INT32 true For a subproject, the number of levels down the hierarchy at which this project occurs. For example, if the project has a single parent project, the value of proj_level is 1.
sec_policy_model INT32 true Stores the selected security policy model for the project template. The following values are valid.
  • 1 = Template Security Model
  • 2 = User Security Model
budget_alloc_granularity INT32 false Specifies the budget granularity for the project. The following values are valid.
  • 0 = quarterly
  • 1 = monthly
  • 2 = weekly
health_status NVARCHAR 32 true The current health status of the project as calculated by the daily automated batch job. This column is the foreign key to uap_proj_health_daily.health_status. This column was added in version 9.0.
health_calc_date DATETIME true The date and time of the last project health calculation. This column was added in version 9.0.
percentage_complete FLOAT true The overall completion percentage that is calculated for the project. This column is the foreign key to uap_proj_health_daily. This column was added in version 9.0.
archival_state NVARCHAR 50 true The archival status of a project to determine whether it is active or inactive.
bpm_setting INT32 true Defaults to 0. This column was added in version 10.0.0.2.
version INT32 false Required for optimistic locking to manage the synchronization of concurrent updates to the table.

uap_proj_job

This table stores data that is generated by the automated batch jobs that calculate project health scores. This column was added in version 9.0 to support features for calculating and displaying an overall project status.

Field Type Length Null? Description
job_id INT32 false The unique, identifying number for the job.
job_type NVARCHAR 50 false
start_date DATETIME false Actual start date of the job.
end_date DATETIME true Actual end date of the job.
tot_proj_count INT32 true
calc_proj_count INT32 true
status NVARCHAR 100 true The status of the job. The following values are valid.
  • In Progress
  • COMPLETED
failure_reason NVARCHAR 1024 true The reason for job failure, if any.

uap_proj_health_rule

This table stores the project health rules added through the user interface. This column was added in version 9.0 to support features for calculating and displaying an overall project status.

Field Type Length Null? Description
health_rule_id INT32 false This column is the foreign key to uap_tt_templates and uap_proj_health_daily.
rule_name NVARCHAR 100 false The supplied identifying name.
description NVARCHAR 1024 true The supplied description.
rule_xml CLOB true The XML for the entered rule.
rule_identifier NVARCHAR 30 true
status NVARCHAR 10 true The status of the rule.
created_by INT32 true User ID of the user who created this rule. This column is the foreign key to uap_users.user_id.
creation_date DATETIME true Time stamp for when the rule was created.
modified_by INT32 true User ID of the user who last modified this rule. This column is the foreign key to uap_users.user_id.
modified_date DATETIME true Time stamp for when the rule was last modified.

uap_proj_health_status

This table stores the labels and colors to associate with the supplied project health status values. This column was added in version 9.0 to support features for calculating and displaying an overall project status.

Field Type Length Null? Description
health_status NVARCHAR 30 false The internal, predefined status. The following values are valid.
  • Healthy
  • Warning
  • Critical
  • Unknown
This column is the foreign key to uap_proj_health_daily.health_status.
status_label NVARCHAR 50 true The name to display in the user interface for the status.
status_color VARCHAR 7 true The color to display in the user interface for the status.
status_weightage INT32 true
is_default INT32 false Identifies the status to assign by default when the conditions in a health status rule do not resolve to a different rule.
seq_no INT32 false Identifier for a health status entry. The system increments the seq_no each time a change is made.
modified_by INT32 true User ID of the user who last modified this rule. This column is the foreign key to uap_users.user_id.
modified_date DATETIME true Time stamp for when the rule was last modified.

uap_proj_health_daily

This table stores the health score for a project as calculated by the daily automated batch job on a specific day. This column was added in version 9.0 to support features for calculating and displaying an overall project status.

Field Type Length Null? Description
project_id INT32 false This column is the foreign key to uap_projects.project_id.
health_rule_id INT32 false This column is the foreign key to uap_proj_health_rule.health_rule_id.
health_status NVARCHAR 30 false This column is the foreign key to uap_proj_health_status.health _status.
day INT32 false The day the daily automated batch job ran and computed the project health scores.
month INT32 false The month the daily automated batch job ran and computed the project health scores.
year INT32 false The year the daily automated batch job ran and computed the project health scores.
percent_task_overdue FLOAT true Percentage of tasks that the daily automated batch job found to be overdue.
percent_milestone_overdue FLOAT true Percentage of milestones that the daily automated batch job found to be overdue.
percent_task_time_overdue FLOAT true Percentage of time that tasks were overdue as computed by the daily automated batch job.
percent_milestone_time_overdue FLOAT true
percent_budget_overrun FLOAT true Total estimated budget minus actual expenses in comparison to the total estimated budget as computed by the daily automated batch job.
percent_project_complete FLOAT true The completion percentage of the overall project.

uap_proj_health_monthly

This table stores health scores and metrics that are calculated for a project during a specific month. This column is used by the Project Health (Monthly) report. This column was added in version 9.0 to support features for calculating and displaying an overall project status.

Field Type Length Null? Description
project_id INT32 false This column is the foreign key to uap_projects.project_id.
month INT32 false The month for which the project health computations were made.
year INT32 false The year for which the health computations were made.
percent_healthy FLOAT true During the specified month, the percentage of time the Healthy status was given to a project.
percent_warning FLOAT true During the specified month, the percentage of time the Warning status was given to project.
percent_critical FLOAT true During the specified month, the percentage of time the Critical status was given to project.
percent_default FLOAT true During the specified month, the percentage of time the Unknown status was given to project.
health_score INT32 false The aggregated health score for the project for the indicated month and year.

uap_format_symbols

This table supports localized date values for the Cognos monthly and trend project health reports. This column was added in version 9.0.

Field Type Length Null? Description
locale NVARCHAR 10 false Identifies a supported locale.
category NVARCHAR 20 false
fs_key INT32 false
fs_value NVARCHAR 50 false

uap_status

Field Type Length Null? Description
status_code VARCHAR 20 false
status_desc NVARCHAR 100 true

uap_notify_attach

Field Type Length Null? Description
notify_id INT32 false
attach_seq INT32 false
user_id INT32 true
attach_date DATETIME true
attach_file NVARCHAR 50 true
attach_path NVARCHAR 255 true
attach_type VARCHAR 50 true
attach_note NVARCHAR 255 true

uap_notifications

This table contains the details for notifications, which are sent as alerts in Unica Plan.

Field Type Length Null? Description
notify_id INT32 false Unique identifier for the notification.
post_datetime DATETIME false Date and time the notification was created.
evt_type INT32 false Type of the event that caused the notification.
evt_post_datetime DATETIME false Date and time the event that caused the notification occurred.
evt_source_type INT32 false
evt_source_id INT32 false
evt_user_id INT32 true
evt_id INT32 true Identifier of the event that caused the notification.
evt_comments CLOB true The text of the notification. The size of this column was increased in version 7.4.1 to support unlimited text length.

uap_notify_rules

Field Type Length Null? Description
notify_rule_id INT32 false
notify_rule_desc NVARCHAR 255 true

uap_member_type

This table defines all the member types for system objects. This table was added in version 7.3.

Field Type Length Null? Description
mem_type_id INT32 false Unique identifier for the membership type.
mem_name NVARCHAR 255 true Name of the membership type. The following values are valid.
  • Approver
  • Manager
  • Owner
  • Participant
  • Recipient
  • Requester
object_type VARCHAR 50 true The object type corresponding to the membership. For example, a program object has membership types of Owner and Participant.
template_id INT32 false

uap_event_queue

Field Type Length Null? Description
event_id INT32 false
event_type INT32 false
post_datetime DATETIME false
src_object_type INT32 false
src_object_id INT32 false
user_id INT32 true
comments CLOB true The size of the column was increased in version 7.4.1. The column now supports unlimited text length.
status VARCHAR 20 true This column was added in version 7.4.0.
plan_server NVARCHAR 50 true For clustered environments, the name of the server for which the item is queued. This column was added in version 7.4.0.

uap_email_queue

Field Type Length Null? Description
email_id INT32 false Unique identifier for the email message.
notify_id INT32 false
post_datetime DATETIME false Date and time for when the message was posted.
resend_count INT32 false
resend_datetime DATETIME false
resend_status INT32 false
to_address NVARCHAR 128 false The size of the column was changed from 64 to 128 in verison 12.1.
from_address NVARCHAR 128 false The size of the column was changed from 64 to 128 in verison 12.1.
reply_address NVARCHAR 1024 false
priority_type INT32 false
subject NVARCHAR 300 false Subject line for the message. The size of the column was changed to 300 in version 7.4.0.
content CLOB false Text content of the message. The size of the column was increased in version 7.3 and again in 7.4.1. The column now supports unlimited text length.
status VARCHAR 20 true This column was added in version 7.4.0.
plan_server NVARCHAR 50 true For a clustered environment, the name of the server for which the item is queued.

uap_system_version

Field Type Length Null? Description
software_version VARCHAR 20 false
database_version VARCHAR 20 false
database_action VARCHAR 20 false
date_performed DATETIME false Column changed to not allows nulls in version 7.4.0.

umcm_item_notify

Field Type Length Null? Description
event_type INT32 false
user_id INT32 false
delivery_mask INT32 true
item_id INT32 false

uap_programs

Program data consists of base data and user-defined custom attributes data. The base data is saved in the uap_programs table and user-defined data is saved in user-defined tables.

Field Type Length Null? Description
program_id INT32 false Unique ID generated by the system for each program.
name NVARCHAR 256 true Size of the column was changed 256 in version 7.4.0.
description NVARCHAR 1024 true
template_name NVARCHAR 50 true Program template name.
program_code NVARCHAR 100 true
status_code VARCHAR 20 true Program status. The following values are valid.
  • LATE
  • OVERDUE
state_code VARCHAR 20 true Program state. The following values are valid.
  • NOT STARTED
  • IN PROGESS
  • ON HOLD
  • CANCELLED
  • COMPLETED
start_date DATETIME true Program target start date.
end_date DATETIME true Program target end date.
actual_st_date DATETIME true Program actual start date.
actual_end_date DATETIME true Program actual end date. Note for start_date, end_date, actual_st_date, and actual_end_date: We only use two of these columns. One set is used and overloaded (both forecast and actual stored there) and there is a relationship to the status_code column.
metric_template NVARCHAR 50 true Program metrics template name. Null if the program does not have any metrics.
created_by INT32 true User who created the program.
created_date DATETIME true Date and time when the program was created. System generated.
modified_by INT32 true User who last modified the program.
last_mod_date DATETIME true Date and time when the program was last modified. System generated.
sec_policy_id INT32 true Project security policy in which the current project is created. The value determines the visibility for the project.
mt_mod_date DATETIME true
mt_finalized_date DATETIME true
budget_alloc_granularity INT32 false Specifies the budget granularity for the plan.
  • 0 = quarterly
  • 1 = monthly
  • 2 = weekly

uap_prgrm_notify

Field Type Length Null? Description
program_id INT32 false Unique ID generated by the system for each program.
event_type INT32 false
user_id INT32 false
delivery_mask INT32 false

uap_prgrm_project

This table contains information about relationships between programs and their associated projects.

Field Type Length Null? Description
project_id INT32 false Unique ID generated by the system for each project. Indicates the ID for a project linked to this program. This column is the foreign key to uap_projects.project_id.
program_id INT32 false Unique ID generated by the system for each program. Foreign key to uap_programs.progam_id.
plan_id INT32 true Plan ID. This value is null if the program is not linked to a plan.
plan_parea_id INT32 true Plan - program area ID. This value is null if the program is not linked to a program area.

uap_prgrm_hist

This table contains details about the program history.

Field Type Length Null? Description
program_id INT32 false Unique ID generated by the system for each Program.
seq_no INT32 false Unique ID generated by the system, for each Program Revision history.
user_id INT32 true Program modified by user.
last_mod_date DATETIME true Program Last Modified date.
status_code VARCHAR 20 true Program status.
comments CLOB true The data type was changed from string (1024) to clob in version 9.1.0.
cur_state VARCHAR 20 true Program current state.
prev_state VARCHAR 20 true Program previous state.

uap_prgrm_users

This table stores details about the program users.

Field Type Length Null? Description
program_id INT32 false Unique ID generated by the system for each program. Foreign key to uap_programs.progam_id.
user_id INT32 false Program team member internal user ID. This column is the foreign key to uap_user.user_id.
mem_type_id INT32 false Program member type role ID. This column is the foreign key to uap_member_type.mem_type_id. The following values are valid.
  • Owner
  • Requestor
  • Participant
  • Observer/Advisor

uap_prgrm_msgboard

Field Type Length Null? Description
program_id INT32 false Unique ID generated by the system for each program.
msg_id INT32 false Unique ID generated by the system, for the program message board.
post_datetime DATETIME false Program message posted date.
user_id INT32 false Message posted by the user. This column is the foreign key to uap_user.user_id.
msg_text NVARCHAR 1024 true Text of the message.
recipients NVARCHAR 512 true List of recipients if the message is emailed.

uap_attach

Field Type Length Null? Description
attach_id INT32 false
attach_orig_file NVARCHAR 255 true
attach_note NVARCHAR 1024 true
attach_size INT32 true
attach_date DATETIME true
mime_type VARCHAR 255 true
flag_mcm_item VARCHAR 1 true
item_id INT32 true
item_version_seq INT32 true
attach_folder_id INT32 false
user_id INT32 true
parent_obj_id INT32 false
parent_obj_type VARCHAR 50 false The size of this column increased to 50 in version 7.4.0.
enable INT32 true Defines the markup state of an attachment for the Markup Anywhere feature. Version 7.3 added this column.

uap_attach_folder

Field Type Length Null? Description
attach_folder_id INT32 false
folder_name NVARCHAR 255 true
parent_obj_id INT32 false
parent_obj_type VARCHAR 50 false The size of this column increased to 50 in version 7.4.0.
parent_folder_id INT32 true

uap_proj_mtc_hist

Field Type Length Null? Description
metric_id NVARCHAR 50 false Metric ID name.
project_id INT32 false Unique ID generated by the system for each project and project request.
hist_datetime DATETIME false
metric_value1 FLOAT true
metric_value2 FLOAT true
metric_value3 FLOAT true
metric_value4 FLOAT true
metric_value5 FLOAT true
metric1_nan NVARCHAR 20 true
metric2_nan NVARCHAR 20 true
metric3_nan NVARCHAR 20 true
metric4_nan NVARCHAR 20 true
metric5_nan NVARCHAR 20 true

uap_invoice_items

This table stores all invoice line items.

Field Type Length Null? Description
invoice_id INT32 false Unique ID generated by the system for each invoice created. This column is the foreign key to uap_invoices.invoice_id.
cost_item_id INT32 false
cost_item_no INT32 true
cost_item_desc NVARCHAR 100 true
src_obj_type VARCHAR 20 true Invoice item source object type. The following values are valid.
  • PROJECT
  • PROGRAM
src_obj_id INT32 true Invoice source object internal ID. This column is the foreign key to uap_project.project_id or uap_programs.program_id, depending on whether the entry is a Project or a Program.
src_obj_code NVARCHAR 100 true Invoice source object code. Values match values from uap_projects.project_code and uap_programs.program_code columns, depending on whether the invoice item source object is a Project or Program.
account_id INT32 true Account internal ID. This column is the foreign key to uap_accounts.account_id.
account_code NVARCHAR 100 true Account code. The values match values from ap_accounts.account_code column.
cost_cat_id INT32 true Cost Category internal ID. This column is the foreign key to uap_cost_cat.cost_cat_id.
cost_cat_code NVARCHAR 100 true Cost Category code. Values: Match values from the uap_cost_cat.cost_cat_code column.
quantity INT32 true Quantity.
unit_cost FLOAT true Item unit cost.
item_cost FLOAT true Item total cost.
approval_id INT32 true The ID of the approval associated with the line item. This column was added in 8.5.0.
approval_status VARCHAR 20 true The status of the approval associated with the line item. This column was added in 8.5.0.
line_item_updated INT32 true A flag to identify updated line items in the edit session. This column was added in 8.5.0. The following are valid values.
  • 1 = the line item is updated
  • 0 = the line item is finalized; not updated
  • -1 = the line item is upgraded

uap_budget_amt

Field Type Length Null? Description
quarter_id INT32 false
year INT32 false
month INT32 false
week INT32 false If budget granularity is not set to week, the value is -1. If the granularity is week, the value is an integer from 0 to 52, denoting the exact week in the fiscal year.
budget_id INT32 false Unique ID generated by the system for each budget created.
forecast_amt FLOAT true
committed_amt FLOAT true
actual_amt FLOAT true
budget_amt FLOAT true
alloc_amt FLOAT true
line_item_amt FLOAT true Used for line item allocations. This column was added in version 7.4.0.

uap_act_budget

Field Type Length Null? Description
account_id INT32 false Unique ID generated by the system for each account created.
budget_id INT32 false Unique ID generated by the system for each budget created.

uap_prgrm_cost

Field Type Length Null? Description
program_id INT32 false
cost_item_id INT32 false
version_id INT32 false
cost_item_no INT32 true
cost_item_desc NVARCHAR 100 true
account_id INT32 true Unique ID generated by the system for each account created.
account_code NVARCHAR 100 true
forecast_amt FLOAT true
committed_amt FLOAT true
expenditure_date DATETIME true
created_date DATETIME true
created_by INT32 true
last_mod_date DATETIME true
modified_by INT32 true
cost_cat_id INT32 true
cost_cat_code NVARCHAR 100 true
vendor_id INT32 true
vendor_code NVARCHAR 100 true
approval_id INT32 true The ID of the approval that is associated with the line item. This column was added in 8.5.0.
approval_status VARCHAR 20 true The status of the approval that is associated with the line item. This column was added in 8.5.0.
line_item_updated INT32 true A flag to identify updated line items in the edit session. This column was added in 8.5.0. The following are valid values.
  • 1 = the line item is updated
  • 0 = the line item is finalized; not updated
  • -1 = the line item is upgraded

uap_parea_budget

Field Type Length Null? Description
budget_id INT32 false Unique ID generated by the system for each budget created.
plan_parea_id INT32 false
budget_total FLOAT true
forecast_total FLOAT true
actual_total FLOAT true
committed_total FLOAT true
finalized_date DATETIME true

uap_proj_cost

Field Type Length Null? Description
project_id INT32 false
cost_item_id INT32 false
version_id INT32 false
line_item_id INT32 false A new column added in the 8.2.0.2 patch to uniquely identify line items.
cost_item_no INT32 true
account_id INT32 true Unique ID generated by the system for each account created.
account_code NVARCHAR 100 true
forecast_amt FLOAT true
committed_amt FLOAT true
expenditure_date DATETIME true
created_date DATETIME true
created_by INT32 true
last_mod_date DATETIME true
modified_by INT32 true
cost_cat_id INT32 true
cost_cat_code NVARCHAR 100 true
cost_item_desc NVARCHAR 100 true
vendor_id INT32 true
vendor_code NVARCHAR 100 true
approval_id INT32 true The ID of the approval associated with the line item. This column was added in 8.5.0.
approval_status VARCHAR 20 true The status of the approval associated with the line item. This column was added in 8.5.0.
line_item_updated INT32 true A flag to identify updated line items in the edit session. This column was added in 8.5.0. The following are valid values.
  • 1 = the line item is updated
  • 0 = the line item is finalized; not updated
  • -1 = the line item is upgraded

uap_invoice_users

This table stores all of the users who are participating in the invoice.

Field Type Length Null? Description
invoice_id INT32 false Unique ID generated by the system for each invoice created. This column indicates the invoice that the user is participating in. This column is the foreign key to uap_invoices.invoice_id
user_id INT32 false User ID of the user. This column is the foreign key to uap_users.user_id.
mem_type_id INT32 false Member type ID of the user for this invoice. This column is the foreign key to uap_member_type.mem_type_id

uap_invoice_hist

Field Type Length Null? Description
invoice_id INT32 false Unique ID generated by the system for each invoice created.
seq_no INT32 false
cur_state VARCHAR 20 true
comments NVARCHAR 1024 true
prev_state VARCHAR 20 true
last_mod_date DATETIME true
user_id INT32 true

uap_quarters

Field Type Length Null? Description
quarter_id INT32 false
year INT32 false
month INT32 false
description NVARCHAR 100 true

uap_account_users

Field Type Length Null? Description
account_id INT32 false Unique ID generated by the system for each account created.
user_id INT32 false
mem_type_id INT32 false

uap_account_hist

This table holds data that tracks account information history.

Field Type Length Null? Description
account_id INT32 false Unique ID generated by the system for each account created.
seq_no INT32 false
comments NVARCHAR 1024 true
last_mod_date DATETIME true
user_id INT32 true

uap_inv_notify

Field Type Length Null? Description
event_type INT32 false
user_id INT32 false
invoice_id INT32 false Unique ID generated by the system for each invoice created.
delivery_mask INT32 true

uap_inv_msgboard

Field Type Length Null? Description
invoice_id INT32 false Unique ID generated by the system for each invoice created.
msg_id INT32 false
post_datetime DATETIME true
user_id INT32 true
msg_text NVARCHAR 1024 true
recipients NVARCHAR 512 true List of recipients if the message is emailed.

uap_invoices

The uap_invoices table is the main table for storing invoice summary details.

Field Type Length Null? Description
invoice_id INT32 false Unique ID generated by the system for each invoice created.
invoice_no NVARCHAR 50 true Invoice number entered by the user in the user interface.
vendor_id INT32 true
po_no NVARCHAR 50 true Purchase order number entered in the user interface.
due_date DATETIME true Invoice due date, entered by the user.
status_code VARCHAR 20 true
state_code VARCHAR 20 true The current state of the invoice. The following values are valid.
  • DRAFT
  • PAYABLE
  • PAID
  • CANCELLED
submit_date DATETIME true Invoice submitted date, entered by the user.
created_by INT32 true User ID of the user who created this invoice. System updates this value. This column is the foreign key to uap_users.user_id
created_date DATETIME true Invoice created date and time. System updates this value.
last_mod_date DATETIME true The last date the invoice was modified, including invoice line item. The system updates this value.
modified_by INT32 true User ID of the user who last modified this invoice, including invoice line items. The system updates this value.
notes NVARCHAR 1024 true
payment_terms NVARCHAR 1024 true
invoice_amount FLOAT true
invoice_code NVARCHAR 100 true
payable_date DATETIME true
paid_date DATETIME true
sec_policy_id INT32 true

uap_prgrm_budget

Field Type Length Null? Description
budget_id INT32 false Foreign key which points to the budget_id column of table uap_budgets
program_id INT32 false Foreign key which points to the program_id column of table uap_programs
budget_total FLOAT true Not used anymore
forecast_total FLOAT true Used to store the forecast total amount (total of forecast amounts for each of the line items included directly and indirectly i.e. in the linked objects)
actual_total FLOAT true Used to store the actual amount (total of actual amount spent for each of the line items included directly and indirectly i.e. in the linked objects)
committed_total FLOAT true Used to store the commited amount(total of committed amount for each of the line items included directly and indirectly i.e. in the linked objects)
alloc_total FLOAT true Not used anymore
line_item_total FLOAT true
finalized_date DATETIME true Date when the budget is finalized

uap_plan_budget

Field Type Length Null? Description
budget_id INT32 false Foreign key which points to the budget_id column of table uap_budgets
plan_id INT32 false Foreign key which points to the plan_id column of table uap_plans
budget_total FLOAT true Not used anymore
forecast_total FLOAT true Used to store the forecast total amount (total of forecast amounts for each of the line items included directly and indirectly i.e. in the linked objects)
actual_total FLOAT true Used to store the actual amount (total of actual amount spent for each of the line items included directly and indirectly i.e. in the linked objects)
committed_total FLOAT true Used to store the commited amount (total of committed amount for each of the line items included directly and indirectly i.e. in the linked objects)
alloc_total FLOAT true Not used anymore
finalized_date DATETIME true Date when the budget is finalized

uap_proj_budget

Field Type Length Null? Description
budget_id INT32 false Foreign key which points to the budget_id column of table uap_budgets
project_id INT32 false Foreign key which points to the project_id column of table uap_projects
budget_total FLOAT true Not used anymore
forecast_total FLOAT true Used to store the forecast total amount (total of forecast amounts for each of the line items included directly and indirectly i.e. in the linked objects)
actual_total FLOAT true Used to store the actual amount (total of actual amount spent for each of the line items included directly and indirectly i.e. in the linked objects)
alloc_total FLOAT true Not used anymore
line_item_total FLOAT true
committed_total FLOAT true Used to store the commited amount (total of committed amount for each of the line items included directly and indirectly i.e. in the linked objects)
finalized_date DATETIME true Date when the budget is finalized

uap_budgets

In general, the budget for an object is a collection of budget amounts for a specific time period. An account has budgets on a monthly basis, while plans, programs, and projects have quarterly budgets. This table contains all budgets across the system, including budgets for accounts, plans, programs, and projects.

Field Type Length Null? Description
budget_id INT32 false Unique ID generated by the system for each budget created.
budget_period VARCHAR 20 true Internal description of the budget period.
created_date DATETIME true Timestamp when the account was created. System generated.
created_by INT32 true User ID of the user who created this account. System generated. This column is the foreign key to uap_users.user_id.
last_mod_date DATETIME true Timestamp when the account was last modified. System generated.
modified_by INT32 true User ID of the user who last modified this account. System generated. This column is the foreign key to uap_users.user_id.

uap_accounts

This table holds the main accounts information.Columns in the uap_accounts table

Field Type Length Null? Description
account_id INT32 false Unique ID generated by the system for each account created.
account_code NVARCHAR 100 false Account code/integer.
account_desc NVARCHAR 1024 true Account description.
src_account_id INT32 true ID of the parent account.
created_date DATETIME true Time stamp when the account was created, generated by the system.
created_by INT32 true User ID of the user who created this account, generated by the system. This column is the foreign key to uap_users.user_id.
last_mod_date DATETIME true Timestamp when the account was last modified, generated by the system.
modified_by INT32 true User ID of the user who last modified this account, generated by the system. This column is the foreign key to uap_users.user_id.
sec_policy_id INT32 true ID of the security policy for this account. This column is the foreign key to uap_sec_policy.sec_policy_id.
flag_enabled VARCHAR 1 true
account_name NVARCHAR 255 true

uap_act_notify

Field Type Length Null? Description
event_type INT32 false
user_id INT32 false
account_id INT32 false Unique ID generated by the system for each account created.
delivery_mask INT32 true

uap_act_msgboard

Field Type Length Null? Description
account_id INT32 false Unique ID generated by the system for each account created.
msg_id INT32 false
post_datetime DATETIME true
user_id INT32 true
msg_text NVARCHAR 1024 true
recipients NVARCHAR 512 true List of recipients if the message is emailed.

uap_plan_accts

Field Type Length Null? Description
plan_id INT32 false
account_id INT32 false

uap_plan_metrics

Field Type Length Null? Description
plan_id INT32 false
metric_id NVARCHAR 50 false
show_as_rollup VARCHAR 1 false
display_name NVARCHAR 100 true
display_order INT32 true
input_method VARCHAR 20 true
formula NVARCHAR 1024 true
units VARCHAR 20 true
display_format VARCHAR 20 true
metric_mprecision INT32 true
description NVARCHAR 1024 true
metric_columns INT32 true
metric_value1 FLOAT true
metric_value2 FLOAT true
metric_value3 FLOAT true
metric_value4 FLOAT true
metric_value5 FLOAT true
metric_rollup1 FLOAT true
metric_rollup2 FLOAT true
metric_rollup3 FLOAT true
metric_rollup4 FLOAT true
metric_rollup5 FLOAT true
metric1_nan NVARCHAR 20 true
metric2_nan NVARCHAR 20 true
metric3_nan NVARCHAR 20 true
metric4_nan NVARCHAR 20 true
metric5_nan NVARCHAR 20 true
planned_value FLOAT true
planned_nan NVARCHAR 20 true
grp_id NVARCHAR 50 true
is_planned_value VARCHAR 1 true
is_visible VARCHAR 1 true No

uap_plan_mt_hist

Field Type Length Null? Description
metric_id NVARCHAR 50 false
plan_id INT32 false
hist_datetime DATETIME false
metric_value1 FLOAT true
metric_value2 FLOAT true
metric_value3 FLOAT true
metric_value4 FLOAT true
metric_value5 FLOAT true
metric_rollup1 FLOAT true
metric_rollup2 FLOAT true
metric_rollup3 FLOAT true
metric_rollup4 FLOAT true
metric_rollup5 FLOAT true
metric1_nan NVARCHAR 20 true
metric2_nan NVARCHAR 20 true
metric3_nan NVARCHAR 20 true
metric4_nan NVARCHAR 20 true
metric5_nan NVARCHAR 20 true

uap_plan_msgboard

Field Type Length Null? Description
plan_id INT32 false Unique identifier generated by the system for each plan.
msg_id INT32 false Unique identifier generated by the system for the message on the message board.
post_datetime DATETIME true Date the message was posted to the message board.
user_id INT32 true Unique identifier for the user who posted the message. This column is the foreign key to uap_user.user_id.
msg_text NVARCHAR 1024 true Text of the message.
recipients NVARCHAR 512 true List of recipients if the message is emailed.

uap_plan_notify

Field Type Length Null? Description
plan_id INT32 false
user_id INT32 false
event_type INT32 false
delivery_mask INT32 true

uap_plan_mt_cols

Field Type Length Null? Description
plan_id INT32 false
col_taborder INT32 false
col_display NVARCHAR 100 true
flag_target VARCHAR 1 true
dimension_type VARCHAR 20 true

uap_parea_mt_cols

Field Type Length Null? Description
col_taborder INT32 false Metrics display order.
plan_parea_id INT32 false
col_display NVARCHAR 100 true Display name of the metric.
flag_target VARCHAR 1 true Flag if this metric is a target.
dimension_type VARCHAR 20 true Dimension type of the metric.

uap_parea_mt_grps

Field Type Length Null? Description
grp_id NVARCHAR 50 false Unique identifier generated by the system for each metric group.
plan_parea_id INT32 false
grp_display NVARCHAR 100 true Display name of the metric group.
grp_display_order INT32 true Display order of the metric group.

uap_parea_metrics

Field Type Length Null? Description
plan_parea_id INT32 false
metric_id NVARCHAR 50 false
show_as_rollup VARCHAR 1 false
display_order INT32 true
input_method VARCHAR 20 true
formula NVARCHAR 1024 true
display_name NVARCHAR 100 true
units VARCHAR 20 true
display_format VARCHAR 20 true
metric_mprecision INT32 true
description NVARCHAR 1024 true
metric_columns INT32 true
metric_value1 FLOAT true
metric_value2 FLOAT true
metric_value3 FLOAT true
metric_value4 FLOAT true
metric_value5 FLOAT true
metric_rollup1 FLOAT true
metric_rollup2 FLOAT true
metric_rollup4 FLOAT true
metric_rollup3 FLOAT true
metric_rollup5 FLOAT true
metric1_nan NVARCHAR 20 true
metric2_nan NVARCHAR 20 true
metric3_nan NVARCHAR 20 true
metric4_nan NVARCHAR 20 true
metric5_nan NVARCHAR 20 true
planned_value FLOAT true
planned_nan NVARCHAR 20 true
grp_id NVARCHAR 50 true
is_planned_value VARCHAR 1 true
is_visible VARCHAR 1 true No

uap_parea_mt_hist

Field Type Length Null? Description
metric_id NVARCHAR 50 false
plan_parea_id INT32 false
hist_datetime TIMESTAMP false
metric_value1 FLOAT true
metric_value2 FLOAT true
metric_value3 FLOAT true
metric_value4 FLOAT true
metric_value5 FLOAT true
metric_rollup1 FLOAT true
metric_rollup2 FLOAT true
metric_rollup3 FLOAT true
metric_rollup4 FLOAT true
metric_rollup5 FLOAT true
metric1_nan NVARCHAR 20 true
metric2_nan NVARCHAR 20 true
metric3_nan NVARCHAR 20 true
metric4_nan NVARCHAR 20 true
metric5_nan NVARCHAR 20 true

uap_plan_parea

Field Type Length Null? Description
plan_parea_id INT32 false
plan_id INT32 false
prgm_area_id INT32 false
display_order INT32 true

uap_plan_prgrm

Field Type Length Null? Description
plan_id INT32 false
program_id INT32 false
plan_parea_id INT32 true

uap_plan_users

Field Type Length Null? Description
plan_id INT32 false
mem_type_id INT32 false
user_id INT32 false

uap_plan_mt_grps

This table contains details about metric groups for plans.

Field Type Length Null? Description
plan_id INT32 false Unique identifier generated by the system for each plan.
grp_id NVARCHAR 50 false Identifier for the metric group.
grp_display NVARCHAR 100 true Display name for the metric group.
grp_display_order INT32 true Display order for the metric group.

uap_plans

Field Type Length Null? Description
plan_id INT32 false
name NVARCHAR 256 true Size of the column was changed to 256 in version 7.4.0.
description NVARCHAR 1024 true
sec_policy_id INT32 true
status_code VARCHAR 100 true
state_code VARCHAR 100 true
metric_template NVARCHAR 100 true
created_date DATETIME true
created_by INT32 true
last_mod_date DATETIME true
fiscal_year INT32 true
plan_code NVARCHAR 100 true
bus_area_id INT32 true
modified_by INT32 true
actual_st_date DATETIME true Actual start date.
actual_end_date DATETIME true Actual end date.
budget_alloc_granularity INT32 false Specifies the budget granularity for the plan.
  • 0 = quarterly
  • 1 = monthly
  • 2 = weekly

uap_initiative

Field Type Length Null? Description
initiative_id INT32 false
name NVARCHAR 255 true
code NVARCHAR 50 true
display NVARCHAR 1024 true
display_no INT32 true
flag_enabled VARCHAR 1 true

uap_prgm_area

Field Type Length Null? Description
prgm_area_id INT32 false
name NVARCHAR 255 true
display NVARCHAR 1024 true
code NVARCHAR 50 true
display_no INT32 true
flag_enabled VARCHAR 1 true

uap_prgrm_metrics

Field Type Length Null? Description
program_id INT32 false Unique ID generated by the system for each program.
metric_id NVARCHAR 50 false
show_as_rollup VARCHAR 1 false
display_name NVARCHAR 100 true
display_order INT32 true
input_method VARCHAR 20 true
formula NVARCHAR 1024 true
units VARCHAR 20 true
display_format VARCHAR 20 true
metric_mprecision INT32 true
description NVARCHAR 1024 true
metric_columns INT32 true
metric_value1 FLOAT true
metric_value2 FLOAT true
metric_value3 FLOAT true
metric_value4 FLOAT true
metric_value5 FLOAT true
metric_rollup1 FLOAT true
metric_rollup2 FLOAT true
metric_rollup3 FLOAT true
metric_rollup4 FLOAT true
metric_rollup5 FLOAT true
metric1_nan NVARCHAR 20 true
metric2_nan NVARCHAR 20 true
metric3_nan NVARCHAR 20 true
metric4_nan NVARCHAR 20 true
metric5_nan NVARCHAR 20 true
planned_value FLOAT true
planned_nan NVARCHAR 20 true
grp_id NVARCHAR 50 true
is_planned_value VARCHAR 1 true
alloc_total FLOAT true
is_visible VARCHAR 1 true

uap_prgrm_mt_hist

Field Type Length Null? Description
metric_id NVARCHAR 50 false
program_id INT32 false Unique ID generated by the system for each program.
hist_datetime DATETIME false
metric_value1 FLOAT true
metric_value2 FLOAT true
metric_value3 FLOAT true
metric_value4 FLOAT true
metric_value5 FLOAT true
metric_rollup1 FLOAT true
metric_rollup2 FLOAT true
metric_rollup3 FLOAT true
metric_rollup4 FLOAT true
metric_rollup5 FLOAT true
metric1_nan NVARCHAR 20 true
metric2_nan NVARCHAR 20 true
metric3_nan NVARCHAR 20 true
metric4_nan NVARCHAR 20 true
metric5_nan NVARCHAR 20 true

uap_prgrm_mt_cols

Field Type Length Null? Description
program_id INT32 false Unique ID generated by the system for each program.
col_taborder INT32 false Metrics display order.
col_display NVARCHAR 100 true Metric display name.
flag_target VARCHAR 1 true Flag if this metric is a target.
dimension_type VARCHAR 20 true Metric dimension type.

uap_prgrm_mt_grps

This table contains details about metric groups for programs.

Field Type Length Null? Description
program_id INT32 false Unique ID generated by the system for each program.
grp_id NVARCHAR 50 false Metric group ID.
grp_display NVARCHAR 100 true Metric group display name.
grp_display_order INT32 true Metric group display order.

uap_inv_rollup

Field Type Length Null? Description
invoice_id INT32 false Unique ID generated by the system for each invoice created.

uap_plan_hist

This table contains details about the plan history.

Field Type Length Null? Description
plan_id INT32 false Unique identifier generated by the system for each plan.
seq_no INT32 false Unique identifier generated by the system for each plan revision history.
user_id INT32 true Unique identifier of the user who modified the plan.
last_mod_date DATETIME true Date on which the plan was last modified.
status_code VARCHAR 20 true Plan status.
comments CLOB true The data type was changed from string (1024) to clob in version 9.1.0.
cur_state VARCHAR 20 true Current state of the plan.
prev_state VARCHAR 20 true Previous state of the plan.

uap_mt_threshold

Field Type Length Null? Description
metric_id NVARCHAR 50 false
threshold_id VARCHAR 20 false
min_value FLOAT true
max_value FLOAT true
color VARCHAR 20 false

uap_managed_list

Field Type Length Null? Description
list_id INT32 false
list_code VARCHAR 20 true
list_name NVARCHAR 255 true
list_table VARCHAR 50 true
key_column VARCHAR 50 true
description NVARCHAR 1024 true
flag_code_first VARCHAR 1 true

uap_ml_bus_area

Field Type Length Null? Description
bus_area_id INT32 false
code NVARCHAR 50 true
name NVARCHAR 255 true
display NVARCHAR 305 true
display_no INT32 true
flag_enabled VARCHAR 1 true

uap_ml_cost_cat

Field Type Length Null? Description
cost_cat_id INT32 false
code NVARCHAR 50 true
name NVARCHAR 255 true
display NVARCHAR 305 true
display_no INT32 true
flag_enabled VARCHAR 1 true

uap_ml_vendor

Field Type Length Null? Description
vendor_id INT32 false
code NVARCHAR 50 true
name NVARCHAR 255 true
display NVARCHAR 305 true
display_no INT32 true
flag_enabled VARCHAR 1 true

uap_rfqs

Field Type Length Null? Description
rfq_id INT32 false
name NVARCHAR 100 true
description NVARCHAR 1024 true
rfq_code NVARCHAR 100 true
due_date DATETIME true
submitted_date DATETIME true
submitted_by INT32 true
status_code VARCHAR 100 true
state_code VARCHAR 100 true
specification CLOB true
response_form_id NVARCHAR 100 true
created_date DATETIME true
created_by INT32 true
last_mod_date DATETIME true
modified_by INT32 true
sec_policy_id INT32 true
fin_vendor_id INT32 true

uap_rfq_users

Field Type Length Null? Description
rfq_id INT32 false
user_id INT32 false
mem_type_id INT32 false

uap_rfq_vendor

Field Type Length Null? Description
rfq_vendor_id INT32 false
rfq_id INT32 false
vendor_id INT32 false
status_code VARCHAR 20 true
response_date DATETIME
comments NVARCHAR 1024 true

uap_rfq_notify

Field Type Length Null? Description
rfq_id INT32 false
user_id INT32 false
event_type INT32 false
delivery_mask INT32 true

uap_rfq_msgboard

Field Type Length Null? Description
rfq_id INT32 false
msg_id INT32 false
post_datetime DATETIME true
user_id INT32 true
msg_text NVARCHAR 1024 true
recipients NVARCHAR 512 true

uap_rfq_hist

Field Type Length Null? Description
rfq_id INT32 false
seq_no INT32 false
user_id INT32 true
last_mod_date DATETIME true
status_code VARCHAR 20 true
comments NVARCHAR 1024 true
cur_state VARCHAR 20 true
prev_state VARCHAR 20 true

uap_proj_role

Field Type Length Null? Description
project_id INT32 false Unique ID generated by the system for each project and project request. This column is the foreign key to uap_projects.
role_id INT32 false Project request role/type ID. This column is the foreign key to uap_ml_role.
role_type INT32 false Role type.

uap_proj_usr_role

Field Type Length Null? Description
project_id INT32 false Unique ID generated by the system for each project and project request.
user_id INT32 false Project role user ID.
role_id INT32 false
role_type INT32 false

uap_wf_role

Field Type Length Null? Description
wf_no INT32 false Unique ID generated by the system for each workflow step or stage. This column is the foreign key to uap_workflow.
role_id INT32 false Workflow task resource role/type ID. This column is the foreign key to uap_ml_role.
role_type INT32 false Type of the role. The following values are valid.
  • 1 = Team Member/Owner
  • 2 = Reviewer/Recipient
role_order INT32 true The order of the role in the approval.

uap_wf_lo_tmp

Field Type Length Null? Description
wf_no INT32 false
link_obj_name NVARCHAR 100 true
link_obj_desc NVARCHAR 1024 true
method INT32 true

uap_wf_lo_user

Field Type Length Null? Description
wf_no INT32 false
user_id INT32 false
required VARCHAR 1 true

uap_appr_role

Field Type Length Null? Description
approval_id INT32 false ID of an approval item. This column is the foreign key to uap_approvals.
role_id INT32 false Approval role ID.
role_type INT32 false Role type.
role_order INT32 true

uap_appr_usr_role

Field Type Length Null? Description
approval_id INT32 false Unique ID generated by the system for each approval item that is created by a user.
user_id INT32 false Approver user ID.
role_id INT32 false Approver role ID.
role_type INT32 false Role type.

uap_ml_role

Field Type Length Null? Description
role_id INT32 false
code NVARCHAR 50 true
name NVARCHAR 255 true
display NVARCHAR 305 true
display_no INT32 true
flag_enabled VARCHAR 1 true

uap_object_prefs

Field Type Length Null? Description
object_id INT32 false
object_type VARCHAR 250 false
pref_name NVARCHAR 250 false
pref_value NVARCHAR 300 true

uap_calendar_config

This table stores attributes used to control the appearance of the Unica Plan calendar. Each row determines the settings for a particular user.

Field Type Length Null? Description
show_weekends INT32 true Determines whether to show weekends when displaying the calendar.
first_day_of_week INT32 true Determines whether the week begins on Sunday (0) or Monday (1).
user_id INT32 false ID of the user to whom these calendar settings apply. Foreign key to uap_user.
default_layout INT32 true Determines the layout of the calendar. The following values are valid.
  • 1 = 1-month text calendar
  • 2 = 1-month graphical calendar
  • 3 = 1-week timeline
  • 4 = 1-month timeline
  • 5 = 1-quarter timeline
  • 6 = 1-fiscal year timeline
  • 7 = 1-calendar year timeline
default_entity_type INT32 true Determines which entities are displayed on the calendar. The following values are valid.
  • 1 = plans
  • 2 = projects
  • 8 = programs
default_search INT32 true ID for the saved search used to filter the items displayed on the calendar. This column is the foreign key to uap_search.search_id.
last_mod_date DATETIME true Date on which this object was last modified.
show_timezone_offset INT32 true This flag determines whether to show the time-zone offset information with every user. If this flag is on, the user's timezone always displays. This column was added in 9.1.0.

uap_search

This table stores saved searches.

Field Type Length Null? Description
search_id INT32 false Unique ID generated by the system for each saved search.
search_criteria CLOB true Criteria used to make up the saved search. The saved search is stored as an XML string, and its length is arbitrarily large.

uap_search_bulk

This table stores saved searches.

Field Type Length Null? Description
search_id INT32 false Unique ID generated by the system for each saved search.
name NVARCHAR 100 true Bulk Search name.
search_criteria CLOB true Criteria used to make up the saved search. The saved search is stored as an XML string, and its length is arbitrarily large.

uap_ml_bd_type

Field Type Length Null? Description
type_id INT32 false
name NVARCHAR 255 false
code NVARCHAR 50 true
display NVARCHAR 305 false
display_no INT32 true
flag_enabled VARCHAR 1 true

uap_blackout_dates

Field Type Length Null? Description
blackout_id INT32 false Unique ID generated by the system, for each blackout (non-work) date.
name NVARCHAR 255 true Name of the blackout/non-work date.
description NVARCHAR 1024 true Description of the blackout/non-work date.
type_id INT32 false Unique ID for the blackout category for this blackout date. Foreign key into uap_ml_bd_type.type_id.
start_date DATETIME false Non-work date start date.
end_date DATETIME false Non-work date end date.
last_mod_date DATETIME false Last modification date of the item.
is_notified INT32 false Not currently used.

uap_cal_color_coding

This table stores information about the attribute used for color coding the calendar.

Field Type Length Null? Description
user_id INT32 false ID of the user to which these calendar settings apply. Foreign key to uap_user.
entity_type_id INT32 false Type of entity used for color coding. The following are valid values.
  • 0 = project
  • 1 = programs
  • 2 = plans
cust_attr_id NVARCHAR 255 false ID of the attribute used for color coding.

uap_cal_view_milestones

This table stores information about displaying milestones on the calendar.

Field Type Length Null? Description
user_id INT32 false ID for the user for whom these calendar settings apply. Foreign key to uap_user.
milestone_type INT32 false Type of milestone displayed on the calendar. This column is the foreign key to uap_wf_milestones.

uap_relation_type

Field Type Length Null? Description
relation_type_id INT32 false
name NVARCHAR 50 false

uap_comp_type

This table contains details about the components in the system, suchh as business processes and marketing objects. This table contains a row for each custom component type as well as a row for each system-defined object, for example, plans and programs. When an administrator creates a marketing object type in Unica Plan, a new row is added to this table.

Field Type Length Null? Description
comp_type_id INT32 false Unique identifier that is generated by the system for each component.
name VARCHAR 50 false Name of the component.
code_module VARCHAR 50 false Type of the component. For built-in components (for example, projects), the value of this column is same as the value of the name column. For marketing objects, the value of this column is components.
status_code VARCHAR 20 false Status for the object. For example, ENABLED.
flag_has_templates VARCHAR 1 false Flag for whether this component has any templates defined.
  • 0 = no templates defined
  • 1 = templates defined
flag_builtin VARCHAR 1 false Flag for whether it is a built-in component:
  • 0 = no (marketing object)
  • 1 = yes

uap_mktgobject

This table contains information about each marketing object in the system.

Field Type Length Null? Description
mktg_object_id INT32 false Unique identifier generated by the system for each marketing object.
name NVARCHAR 390 false Name of the marketing object. The size of this column increased in version 8.6.0.
description NVARCHAR 1536 true Description of the marketing object. The size of this column increased in version 8.6.0.
template_name NVARCHAR 50 false Name of the template used to create the marketing object.
object_code NVARCHAR 100 false
sec_policy_id INT32 false
status_code VARCHAR 20 false Status of the marketing object.The following values are valid.
  • ACTIVE
  • DELETED
state_code NVARCHAR 20 false State of the marketing object. The valid values depend on the marketing object.
created_by INT32 false Unique identifier for the user who created the marketing object.
created_date DATETIME false Date and time the marketing object was created.
modified_by INT32 false Unique identifier for the user who last modified the marketing object.
lastmod_date DATETIME false
comp_type_name VARCHAR 50 false Marketing object type of the marketing object.
parent_folder_id INT64 true This column was added in version 8.6.0.

uap_mo_hist

This table contains details about the history of marketing objects.

Field Type Length Null? Description
mktg_object_id INT32 false Unique identifier generated by the system for each marketing object. This column is the foreign key to uap_mktgobject.
seq_no INT32 false Identifier for a history entry. The system increments the seq_no for each new history item.
user_id INT32 false Unique identifier for the user who modified the marketing object. This column is the foreign key to uap_user.
on_behalf_user_id INT32 true
last_mod_date DATETIME false Date the marketing object was last modified.
status_code VARCHAR 20 true Status of the marketing object.
comments CLOB true Revision history description.
cur_state NVARCHAR 20 true
prev_state NVARCHAR 20 true Previous state of the marketing object.

uap_mo_notify

Field Type Length Null? Description
mktg_object_id INT32 false Unique identifier generated by the system for each marketing object. This column is the foreign key to uap_mktgobject.
event_type INT32 false
user_id INT32 false This column is the foreign key to uap_user.
delivery_mask INT32 false

uap_mo_users

This table contains the basic data for all marketing object users.

Field Type Length Null? Description
user_id INT32 false Internal user ID for a marketing object member. This column is the foreign key to uap_user.
mktg_object_id INT32 false Unique identifier generated by the system for each marketing object. This column is the foreign key to uap_mktgobject.
mem_type_id INT32 false Marketing object member type role ID. This column is the foreign key to uap_member_type. The following values are valid.
  • Owner
  • Participant

uap_mo_msgboard

Field Type Length Null? Description
msg_id INT32 false Unique identifier generated by the system for marketing object message board messages.
mktg_object_id INT32 false
post_datetime DATETIME false Date the message was posted.
user_id INT32 false Unique identifier for the user who posted the message. This column is the foreign key to uap_user.
msg_text NVARCHAR 1024 true Text of the message.
recipients NVARCHAR 512 true List of recipients if the message was emailed.

uap_ref_instance

This table contains a row for each source and destination object for all reference relationships.

Field Type Length Null? Description
comp_inst_id INT32 false Unique identifier generated by the system for each object instance.
comp_type_id INT32 false Identifier for the component type. This column is the foreign key into uap_comp_type.
status_code VARCHAR 20 false Object status. The following values are valid.
  • ACTIVE
  • DELETED
name NVARCHAR 390 false Name of the object. The size of this column increased in version 8.6.0.
state_code NVARCHAR 20 false Object state. Valid values depend on the object type.

uap_ref_meta

Field Type Length Null? Description
ref_meta_id INT32 false
template_id INT32 false
form_id INT32 false
field_id VARCHAR 50 false

uap_comp_ref

This table contains details about marketing objects that are referenced in other objects, such as projects or other marketing objects. It contains a row for each reference relationship between any two objects.

Field Type Length Null? Description
relation_type_id INT32 false This column is the foreign key to uap_relation_type. The following are valid values.
  • 1 = Modifies
  • 2 = References
dest_comp_id INT32 false Identifier for the destination object for this reference relationship.
src_comp_id INT32 false Identifier for the source object for this reference relationship.
src_comp_type_id INT32 false Component type for the destination object.
dest_comp_type_id INT32 false Component type for the source object.
ref_meta_id INT32 true If the reference is in a grid, this is the identifier for the grid.
grid_row_id INT32 true If the reference is in a grid, this is the identifier for the row in the grid that contains the reference.
grid_column_id VARCHAR 255 true If the reference is in a grid, this is the identifier for the column in the grid that contains the reference.

uap_user_tvc_pref

This table was added in version 7.3.

Field Type Length Null? Description
pref_id INT32 false
user_id INT32 false
form_id INT32 false
tvc_id NVARCHAR 255 false
group_by NVARCHAR 255 true
sort_by NVARCHAR 255 true
sort_order INT32 true

uap_user_tvc_columns

This table was added in version 7.3.

Field Type Length Null? Description
pref_id INT32 false
tvc_column_id NVARCHAR 255 false
order_index INT32 false
visible VARCHAR 1 true

uap_teams

This table contains information about teams.

Field Type Length Null? Description
team_id INT32 false Unique team identifier.
name NVARCHAR 256 true The team name as it appears in Unica Plan. The size of the column was changed to 256 in version 7.4.0.
description NVARCHAR 256 true Text description for the team.
skillsets NVARCHAR 256 true Free form text describing the skills for the team.
status INT32 true Status of the team. The following values are valid.
  • 1 = Enabled
  • 2 = Disabled
  • 3 = Deleted

uap_team_members

This table lists all team members, and their membership type, for each team in the system.

Field Type Length Null? Description
team_id INT32 false Unique identifier for the team. This column is the foreign key into uap_teams.team_id.
user_id INT32 false Team member ID. This column is the foreign key to uap_users.user_id.
mem_type_id INT32 false Member type ID of the team for this project. This column is the foreign key to uap_member_type.mem_type_id.

uap_team_security_policy

This table lists each team and the security policy that applies for that team.

Field Type Length Null? Description
team_id INT32 false Unique identifier for the team. This column is the foreign key into uap_teams.team_id
policy_id INT32 false Unique identifier for the security policy. This column is the foreign key to uap_sec_policy.sec_policy_id.

uap_team_work_model

This table lists each work routing model used by each team.

Field Type Length Null? Description
team_id INT32 false Team identifier for the team. For each work routing model the team uses, there is a row in this table. For example, if team 10001 uses all three work routing models, this table has three rows with the 10001 team_id. This column is the foreign key into uap_teams.team_id.
model INT32 false Describes the work routing model for the team. The following values are valid.
  • 1 = Managers can route work to members
  • 2 = Members can accept team-assigned tasks
  • 3 = Members can return team-assigned tasks back to the team

uap_team_history

This table contains details about the history of teams.

Field Type Length Null? Description
history_id INT32 false Unique ID generated by the system for each edit to a team.
sequence_no INT32 false Sequence of events for each team.
team_id INT32 false Unique identifier for the team. This column is the foreign key into uap_teams.team_id.
activity INT32 true ID for the activity that occurred for this event. The following values are valid.
  • 1 = team created
  • 2 = team deleted
  • 3 = team edited
  • 4 = team copied
  • 5 = team enabled
  • 6 = team disabled
user_id INT32 true User who modified the marketing object.
on_behalf_user_id INT32 true This column was added in version 7.4.0.
field_changed VARCHAR 255 true Name of the field that was edited.
old_value NVARCHAR 256 true Previous value for the edited field.
new_value NVARCHAR 256 true Replacement value for the edited field.
date_time DATETIME true Date the change was made.

uap_team_task_routing

Field Type Length Null? Description
task_audit_id INT32 false
task_id INT32 false
assigned_to INT32 true Team ID. This column is the foreign key into uap_teams.team_id.
is_user INT32 true
user_team_id INT32 true
routed_by INT32 true
date_time DATETIME true
state INT32 true
sequence_no INT32 false

uap_wf_teams

This table stores information about teams and users assigned to workflow tasks. This table was added in version 7.3.

Field Type Length Null? Description
wf_no INT32 false Unique ID generated by the system for each workflow step or stage. This column is the foreign key to uap_workflow.
team_id INT32 false Unique identifier for the team. This column is the foreign key into uap_teams.
user_id INT32 false User ID. This column is the foreign key into uap_users.

uap_proj_teams

Field Type Length Null? Description
project_id INT32 false Unique project identifier. This column is the foreign key to uap_projects.project_id.
team_id INT32 false Unique identifier for the team. This column is the foreign key to uap_teams.team_id.
mem_type_id INT32 false Member type ID of the team for this project. This column is the foreign key to uap_member_type.mem_type_id.

uap_proj_team_role

Field Type Length Null? Description
project_id INT32 false Unique project identifier. This column is the foreign key to uap_projects.project_id.
team_id INT32 false Unique identifier for the team. Foreign key to uap_teams.team_id.
role_id INT32 false Unique identifier for the role. Foreign key to uap_ml_role.role_id.
role_type INT32 false Role type ID.

uap_team_alert_rule

Field Type Length Null? Description
team_id INT32 false
mem_type_id INT32 false
event_type INT32 false
delivery_mask INT32 false

uap_team_notify

Field Type Length Null? Description
event_type INT32 false
team_id INT32 false Unique identifier for the team. This column is the foreign key to uap_teams.team_id.
user_id INT32 false This column is the foreign key to uap_users.user_id.
delivery_mask INT32 false

uap_trigger_defn

Field Type Length Null? Description
trigger_id INT32 false Unique ID for a trigger.
trigger_name NVARCHAR 64 false Text name for a trigger.
trigger_desc NVARCHAR 1024 true Text description for a trigger.
event_type VARCHAR 32 true
object_type VARCHAR 32 true
condition_class VARCHAR 255 false
action_class VARCHAR 255 false

uap_trigger_binding

Field Type Length Null? Description
trigger_id INT32 false This column is the foreign key into uap_trigger_defn table.
event_type VARCHAR 32 true
object_type VARCHAR 32 true
object_id INT32 false

uap_trigger_params

Field Type Length Null? Description
param_id INT32 false
trigger_id INT32 false Foreign key to uap_trigger_defn table.
param_name NVARCHAR 255 false Text name for a parameter.
param_class VARCHAR 255 false
param_value NVARCHAR 1024 true

uap_tt_triggers

Field Type Length Null? Description
template_id INT32 false
trigger_id INT32 false

uap_object_lock

This table stores information to support object locking; persistent object locking is used in clustered Unica Plan environments. This table was added in version 7.4.0.

Field Type Length Null? Description
user_id INT32 false The ID of the user who locked this object. This column is the foreign key to uap_user.
object_type VARCHAR 50 false object type; ObjecttypeEnum
object_id INT32 false Unique ID of the object. The table to which this is a foreign key depends on the object type.
tab_id NVARCHAR 50 false The ID of the tab that is locked, for example, summary or tracking. The default value, used when the object does not have tabs, is summary. You can also use this column to uniquely identify the non-tab item.
session_id VARCHAR 200 true HTTP session ID.
parent_object_type VARCHAR 50 true The object type of this object's parent. If this object does not have a parent object, the value of this column is null.
parent_object_id INT32 true The object ID of this object's parent object. The table to which this is a foreign key depends on the object type. If the current object does not have a parent object, the value of this column is -1.
locked_time VARCHAR 20 true The string value of the system timestamp for the date and time when the object was locked. The column cannot use datetime because after saving System.currentTimeMillis() to the database, the millisecond part is incorrect. This column is used to check broken lock.
host_ip VARCHAR 15 true IP address of the machine on which the Unica Plan server is running.
plan_server VARCHAR 50 true Name of the server (for WebLogic) or node (for WebSphere).
client_ip VARCHAR 15 true IP address of the client machine that connected to the server.
lock_owner VARCHAR 20 true Who requested the lock. The following values are valid.
  • ui
  • api
breakable VARCHAR 1 true Whether the lock is breakable. The following values are valid.
  • Y = Yes, breakable
  • N = No
lock_level VARCHAR 20 true The level at which to lock the object. The following values are valid.
  • object
  • tab
If lock_level="object", the lock key is object type + object ID. If lock_level="tab", the lock key is object type + object ID + tab ID.
display NVARCHAR 256 true
object_independent VARCHAR 1 true

uap_team_object_routing

New in version 7.4.0. This table contains details about how teams route work.

Field Type Length Null? Description
object_audit_id INT32 false Unique ID generated by the system for each team routing model.
object_id INT32 false Object ID for the object that the team is assigned.
object_type VARCHAR 20 true Object type of the object on which the team is working.
assigned_to INT32 true
is_user INT32 true
user_team_id INT32 true
routed_by INT32 true
date_time DATETIME true
state INT32 true
sequence_no INT32 false Sequence of events for each routing model.

uap_appr_teams

This table was added in version 7.4.0 and contains details for team approvals.

Field Type Length Null? Description
approval_id INT32 false Approval ID.
team_id INT32 false Team ID.
mem_type_id INT32 false

uap_appr_team_role

This table contains details about roles for team approvals. This table was added in version 7.4.0.

Field Type Length Null? Description
approval_id INT32 false ID of an approval item. This column is the foreign key to uap_approvals.
team_id INT32 false Team ID.
role_id INT32 false Approval role ID.
role_type INT32 false Role type.

uap_wf_lo_team

Field Type Length Null? Description
wf_no INT32 false
team_id INT32 false
required VARCHAR 1 true

uap_procedure_binding

This table contains the details for the trigger bindings that are defined in Unica Plan. This table was added in version 7.4.0.

Field Type Length Null? Description
pb_id INT32 false Unique ID for the trigger binding.
name NVARCHAR 256 false
object_type VARCHAR 256 true
template NVARCHAR 256 true
context_type NVARCHAR 256 true
basic_event_type NVARCHAR 256 true
exclusivo VARCHAR 1 true Boolean value to indicate whether the trigger binding is exclusive.
deferred VARCHAR 1 true
procedure_class VARCHAR 255 false

uap_lkup_manager

This table stores information for the Unica Plan Lookup Data Management module. This table was added in version 7.4.1.

Field Type Length Null? Description
map_id INT32 false Unique ID of the form that contains the attribute.
internal_attr_name NVARCHAR 100 false Internal name of the attribute that contains the list of values.
lkup_table_name NVARCHAR 100 false
value_id NVARCHAR 100 false ID of the individual value of the attribute.

uap_default_alerts

This table stores default alert messages for each locale. This table was added in version 7.5.

Field Type Length Null? Description
event_id INT32 false Unique identifier for the event.
locale VARCHAR 10 false Locale in which the message is used.
object_type INT32 false Object type of the event.
subject_general CLOB false Subject of the alert message.
subject_exclusive CLOB true Personalized subject of the alert message.
body_general CLOB true Body message of the alert message.
body_exclusive CLOB true Personalized body message of the alert message.

uap_customized_alerts

This table stores the alert messages customized by the user. This table was added in version 7.5.

Field Type Length Null? Description
event_id INT32 false This column is the primary key. Event ID.
locale VARCHAR 10 false This column is the primary key. Locale of the message.
template_id INT32 false Template ID. This column is the foreign key from uap_tt_template.
object_type INT32 false This column is the primary key. Object type of the event.
subject_general CLOB false Subject of the alert message.
subject_exclusive CLOB true Personalized subject of the alert message.
body_general CLOB true Body message of the alert message.
body_exclusive CLOB true Personalized body message of the alert message.

uap_cust_alert_header_footer

This table was added in version 7.5.

Field Type Length Null? Description
template_id INT32 false This column is the primary key. Template ID. This column is the primary key from uap_tt_template.
locale VARCHAR 10 false
header CLOB true
footer CLOB true

uap_tt_proj_req

This table stores project template request setup data. This table was added in version 7.5.

Field Type Length Null? Description
req_setup_id INT32 false Unique identifier generated by the system for each row in the table.
template_id INT32 false Template ID for the project template. This column is the foreign key to uap_tt_template.
description NVARCHAR 300 true The request description of the project template.
reapproval_rule INT32 false Template settings related to the reapproval rule.
add_del_recp VARCHAR 1 true Template settings related to "add/delete recipients" rights to user.

uap_recp_setting

This table stores recipient settings configured by the administrator. This table was added in version 7.5.

Field Type Length Null? Description
step_id INT32 false Unique identifier generated by the system for each row in the table.
template_id INT32 false Unique identifier for the template. This column is the foreign key to uap_tt_template.
role_id INT32 true Role ID for the role of the selected recipient. This column is the foreign key to uap_roles.
assignment_id INT32 false The method by which this recipient was assigned the request. The following values are valid.
  • R = Requester Assigned
  • A = Admin Assigned
  • U = Rule Assigned
user_id INT32 true If recipient was added by admin, then that user ID would be in this column.
team_id INT32 true If recipient was added by admin, then that team ID would be in this column.
rule_id INT32 true Unique identifier for the rule that assigned the recipient. This column is the foreign key to uap_template_rule.
duration VARCHAR 20 false Duration for the reviewer step.
required VARCHAR 1 true Whether the recipient is required or optional.
sequence INT32 false Sequence of the recipient.
proj_owner VARCHAR 1 true Whether this recipient will become the project owner.
instructions NVARCHAR 1024 true Instructions for reviewers by admin.

uap_proj_recp

This table stores recipients settings while creating project requests. This table was added in version 7.5.

Field Type Length Null? Description
step_id INT32 false Unique identifier generated by the system for each recipient step.
project_id INT32 true Unique identifier for the project that contains this recipient step. This column is the foreign key to uap_projects.
assignment_type INT32 true Type of assignment.
role_id INT32 true This column is the foreign key to uap_ml_role. Where multiple roles are added.
rule_id INT32 true This column is the foreign key from rule table.
duration VARCHAR 1024 false Duration for the recipient step.
sequence INT32 false Sequence of recipient.
required VARCHAR 1 true Whether recipient is required or optional.
proj_owner VARCHAR 1 true Whether recipient will become project owner.
instructions NVARCHAR 1024 true Instructions for recipient by admin or by request owner.
submitted_date DATETIME true Date on which request is submitted to recipient for response.
accept_deny VARCHAR 1 true Recipient's reply (accepted or denied) to the project request.The following values are valid.
  • accepted
  • denied
user_comments CLOB true Comments entered by the recipient while responding to the request. The data type was changed to CLOB in version 9.0.0.
responded_date DATETIME true Date on which recipient submitted the response.
scheduled_due_date DATETIME true Due date for the response of the recipient. This date is calculated based on the submitted date and the duration of the recipient step.

uap_tt_wf_rule

This table stores recipients rules configured by the administrators. This table was added in version 7.5.

Field Type Length Null? Description
rule_id INT32 false Unique identifier generated by the system for each recipients rule.
template_id INT32
rule_xml CLOB false

uap_template_function

This table stores the template-level functions. These functions map to functions defined in uap_function. This table was added in version 7.5.

Field Type Length Null? Description
template_function_id INT32 false
function_id INT32 false
template_id INT32 false
object_type VARCHAR 100 false
map_id INT32 false

uap_tt_role_member_type_map

This table was added in version 7.5.

Field Type Length Null? Description
role_id INT32 false
template_id INT32 false
flag_team_mem VARCHAR 1 false
object_type VARCHAR 20 false
mem_type_id INT32 false

uap_tt_workflow_template

Field Type Length Null? Description
workflow_id INT32 false
name NVARCHAR 50 false
taskcode_prefix NVARCHAR 100 true
status_flag INT32 false
created_date DATETIME true
last_mod_date DATETIME true

uap_user_grid_pref

This table stores user preferences for the editable grid component. This table was added in version 7.5.

Field Type Length Null? Description
user_id INT32 false User ID of the user that stored the preferences. This column is the primary key from uap_user.
form_id INT32 false Form ID of the form that contains the grid. This column is the primary key from uap_tt_map.
tvc_id NVARCHAR 255 false A unique identifier that distinguishes among multiple grids on the same form.
pref_mode NVARCHAR 10 false The mode for which preferences were saved. The following values are valid.
  • view
  • edit
As of version 7.5 only view mode preferences can be saved.
pref_value CLOB true The CLOB column that stores the user preferences value. The user preferences value is stored as XML.

uap_mcm_annotations

This table stores attributes common to all types of annotations. This table was added in version 7.5.0.

Field Type Length Null? Description
annotation_id INT32 false ID of the annotation.
author INT32 false User ID of plan user who created the annotation. This column is the primary key from uap_user.
body NVARCHAR 1024 false The text of the annotation.
coordinate_id INT32 false The positioning of this annotation. This column is the primary key from uap_mcm_coordinates.
heading NVARCHAR 150 false The title of the annotation.
created_date DATETIME false The date that the annotation was created.
last_modified DATETIME true The last date on which the annotation was modified.
last_modified_user INT32 true The user ID of person who last modified the annotation. This column is the primary key from uap_user.
last_updated_desc NVARCHAR 1024 true
active INT32 false A filter criteria. The following values are valid.
  • 0=Archived
  • 1=Active
mcm_source_id NVARCHAR 1000 false The approval_id + document_id + page no. Each page of the document is a source and can have annotations. The size of the column was increased to 1000 in version 12.0.
parent_id INT32 false Annotations can have a hierarchy of nested replies. Each reply points to its parent. The value is -1 for the first (topmost) annotation.
top_most_parent_id INT32 false This column points to the topmost parent invitation. It is useful to identify all the nested replies of an annotations at the same time, for example, if you need to delete them. The value is -1 for the first (topmost) annotation.
annotation_type INT32 false
  • Note = 1
  • Scribble = 2

uap_mcm_attachments

Table stores the attachments linked to annotations. Even replies can have attachments. This table was added in version 7.5.0.

Field Type Length Null? Description
attachment_id INT32 false
content_type NVARCHAR 128 false
original_file_name NVARCHAR 1024 false
annotation_id INT32 false

uap_mcm_coordinates

This table stores the coordinates of annotations that can be positioned. It was added in version 7.5.0.

Field Type Length Null? Description
coordinate_id INT32 false The ID of the coordinate.
x float NOT_NULL
y float NOT_NULL
tau float ALLOW_NULL

uap_mcm_scribble

This table stores the strokes for annotations of the type of scribble or free draw. This table was added in version 7.5.0.

Field Type Length Null? Description
scribble_id INT32 false The ID of the scribble.
sigma CLOB true
annotation_id INT32 false

uap_mcm_todo

This table stores attributes specific to ToDo type of annotations. This table was added in version 10.0.0.

Field Type Length Null? Description
annotation_id INT32 false ID of the annotation.
assignee INT32 false User ID of plan user who is the assignee of the annotation. This column is the primary key from uap_user.
due_date DATETIME true The due date of the annotation.
status NVARCHAR 20 false The status of this annotation.
comments NVARCHAR 1024 false The comments of the annotation.

uap_costitem_ext_col

This table stores information about text-only columns that template administrators add to the Budget tab line item tables for programs and projects. This table was added in version 8.0.

Field Type Length Null? Description
ext_col_id INT32 false Unique identifier that is generated by the system for this column.
name NVARCHAR 25 false Label of the column.
status INT32 false Flag for whether the column is enabled. The following values are valid.
  • 1 = Enabled
  • 2 = Disabled

uap_costitem_ext_col_val

This table stores the values that users enter in any additional text-only columns that template administrators add to the Budget tab line item tables for programs or projects. Values that are entered in the standard columns of line item tables are stored in either the uap_proj_cost_item or uap_prgm_cost_item table.

Field Type Length Null? Description
object_type VARCHAR 10 false
object_id INT32 false Unique identifier for a program or project.
cost_item_id INT32 false Unique identifier for a row in the line items table. Foreign key to uap_proj_cost or uap_prgm_cost.
version_id INT32 false Version ID. This column is the foreign key to uap_proj_cost or uap_prgm_cost.
ext_col_id INT32 false Unique identifier for the additional column. This column is the foreign key to uap_costitem_ext_col.
value NVARCHAR 150 false Value that is entered by the user in the column.

uap_deleted_tcs_rows

This table stores the cell IDs of published rows in a Target Cell Spreadsheet deleted since the TCS was last published. The next time the TCS is published, Unica Plan sends the rows in this table for that project to Unica Campaign for deletion. If the deletion is successful, Unica Plan then deletes thoe rows from this table.This table was added in version 8.0.

Field Type Length Null? Description
uap_tcs_cell_id INT64 false Identifier for the deleted cell in the TCS.
uap_project_id INT32 false Unique identifier for the project that contains the TCS.

uap_campaign_std_attributes

This table stores the values for the default campaign attributes for each campaign project. The table does not have the campaign name or campaign code, which are identical to the project name and project code. The project name and project code are stored in the table uap_projects.

Field Type Length Null? Description
project_id INT32 false Unique identifier for the campaign project. This column is the foreign key to uap_projects.
description NVARCHAR 1024 true Description of the campaign.
start_date DATETIME true Start date of the campaign.
end_date DATETIME true End date of the campaign.
objective NVARCHAR 1024 true Objective of the campaign.
initiative NVARCHAR 255 true Initiative of the campaign.
security_policy_id INT32 true Unique identifier of the security policy for the campaign.
security_policy_name NVARCHAR 192 true Name of the security policy for the campaign.

uap_campaign_offers

This table stores information about offers and offer lists coming from Unica Campaign. The table is populated when a user selects one or more offers from the offer dialog in the Target Cell Spreadsheet and saves the changes. The table is updated when the TCS is published if the publish TCS call returns an updated name for an offer or offer list. This table was added in version 8.0.

Field Type Length Null? Description
offer_gen_id INT32 false Unique identifier generated by Unica Plan for the offer.
uac_offer_id INT64 true Unique identifier of the offer in Unica Campaign
offer_name NVARCHAR 390 true Name of the offer.
offer_code NVARCHAR 50 true Code of the offer.
offer_type INT32 true Type of offer: offer or offer list.
partition_id NVARCHAR 256 false Stores the partition of the selected offer. The size of this column increased in version 8.6.0.

uap_campaign_offer_ref

This table is the junction table between the offer assigned to a row in the Target Cell Spreadsheet and the uap_campaign_offers table.

Field Type Length Null? Description
dest_offer_gen_id INT32 false Unique identifier generated by Plan for the offer. Refers to the uap_campaign_offers table.
src_project_id INT32 false Unique identifier of the source project.
grid_row_id INT32 false Unique identifier of the row in the TCS for which the offer is assigned.

lkup_flowchart_run_type_en_us

This table stores the en_us display text for each run type received from Unica Campaign. This table was added in version 8.0.

Field Type Length Null? Description
run_type_id INT32 false Unique identifier for the run type.
run_type_value NVARCHAR 30 false Internal name for the run type.
run_type_text NVARCHAR 50 false Localized display text for the run type.

lkup_flowchart_run_type_en_gb

Field Type Length Null? Description
run_type_id INT32 false
run_type_value NVARCHAR 30 false
run_type_text NVARCHAR 50 false

lkup_flowchart_run_type_fr_fr

Field Type Length Null? Description
run_type_id INT32 false
run_type_value NVARCHAR 30 false
run_type_text NVARCHAR 50 false

lkup_flowchart_run_type_de_de

Field Type Length Null? Description
run_type_id INT32 false
run_type_value NVARCHAR 30 false
run_type_text NVARCHAR 50 false

lkup_flowchart_run_type_es_es

Field Type Length Null? Description
run_type_id INT32 false
run_type_value NVARCHAR 30 false
run_type_text NVARCHAR 50 false

lkup_flowchart_run_type_pt_br

Field Type Length Null? Description
run_type_id INT32 false
run_type_value NVARCHAR 30 false
run_type_text NVARCHAR 50 false

lkup_flowchart_run_type_it_it

Field Type Length Null? Description
run_type_id INT32 false
run_type_value NVARCHAR 30 false
run_type_text NVARCHAR 50 false

lkup_flowchart_run_type_ja_jp

Field Type Length Null? Description
run_type_id INT32 false
run_type_value NVARCHAR 30 false
run_type_text NVARCHAR 50 false

lkup_flowchart_run_type_ko_kr

Field Type Length Null? Description
run_type_id INT32 false
run_type_value NVARCHAR 30 false
run_type_text NVARCHAR 50 false

lkup_flowchart_run_type_zh_cn

Field Type Length Null? Description
run_type_id INT32 false
run_type_value NVARCHAR 30 false
run_type_text NVARCHAR 50 false

lkup_flowchart_run_type_zh_tw

This table stores the Traditional Chinese language (zh_tw) display text for each run type that is received from Unica Campaign. This table was added in version 9.1.1.

Field Type Length Null? Description
run_type_id INT32 false Unique identifier for the run type.
run_type_value NVARCHAR 30 false Internal name for the run type.
run_type_text NVARCHAR 50 false Localized display text for the run type.

lkup_flowchart_run_type_ru_ru

This table stores the Russian language (ru_ru) display text for each run type that is received from Unica Campaign. This table was added in version 8.6.

Field Type Length Null? Description
run_type_id INT32 false Unique identifier for the run type.
run_type_value NVARCHAR 30 false Internal name for the run type.
run_type_text NVARCHAR 50 false Localized display text for the run type.

uap_ml_milestone

This table was added in version 8.1.0. Columns in the uap_ml_milestone table

Field Type Length Null? Description
milestone_id INT32 false This column is the primary key for milestone entry.
code NVARCHAR 50 false User-defined code for the milestone.
name NVARCHAR 255 true Name of the milestone.
display NVARCHAR 305 true Display format of the milestone: name-code or code-name.
display_no INT32 true Sequence number.
flag_enabled VARCHAR 1 true The following values are valid.
  • 1 = enabled
  • 0 = disabled

uap_budget_alloc_version

This table was added in version 8.2.0.

Field Type Length Null? Description
budget_alloc_version_id INT32 false This column is the primary key for a budget allocation version.
owner_object_id INT32 false The plan, program, or project id that owns this allocation version.
owner_object_type VARCHAR 50 false The object type to which this allocation version belongs: project, program, or plan.
fiscal_year INT32 false The fiscal year for which this version was created.
version_label NVARCHAR 50 false The name of the version as provided by the user.
state_code VARCHAR 20 false Status of the version. The following values are valid.
  • NON_FINALIZED
  • ACTIVE
  • FINALIZED
approved INT32 true Indicates whether this budget version is approved. This column was not used in version 8.2.0. It is reserved for future usage.
created_date DATETIME true Creation date of this version.
created_by INT32 true ID of the user who created this version.
last_mod_date DATETIME true Date of the last modification.
modified_by INT32 true ID of the user who made the last modification.

uap_budget_period

This table tracks of budget periods across financial year. This table was added in version 8.2.0.Note that quarter_id, month, and week values denote the relative values within a fiscal year, so a month value of 0 does not necessarily mean January. A month value of 0 indicates the first month of the fiscal year. The quarter, month, and week representations in this table are relative. These values are not the absolute calendar values.

Field Type Length Null? Description
budget_period_id INT32 false This column is the primary key for a budget period.
fiscal_year INT32 false The fiscal year.
quarter_id INT32 false The quarter of the fiscal year; if budget granularity is quarterly, valid values are 0, 1, 2, and 3. If the granularity is not quarterly, the value is -1.
month INT32 false The month of the fiscal year. If the budget granularity is monthly, valid values are 0 through 11. If the granularity is not monthly, the value is -1.
week INT32 false The week of the fiscal year. If the budget granularity is weekly, valid values are 0 through 52. If the granularity is not weekly, the value is -1.
period_granularity INT32 false A flag that denotes the period granularity. The following values are valid.
  • 0 = quarterly
  • 1 = monthly
  • 2 = weekly

uap_budget_spend

This table tracks committed, forecasted, and actual spending. This table was added in version 8.2.0.

Field Type Length Null? Description
budget_id INT32 false The budget_id of the object for which the spending amount is being tracked.
budget_period_id INT32 false The budget_period to which this spending can be attributed. This column is the foreign key reference to uap_budget_period, budget_period_id.
forecast_amt FLOAT true Predicted spending incurred going forward.
committed_amt FLOAT true Represents the amounts that are committed to be spent.
actual_amt FLOAT true Funds already spent.

uap_budget_allocation

This table tracks allocations to various objects for different budget periods in different budget allocation versions. Version 8.2 added this table.For example, the alloc_amt values are populated for child values of the object, but not for the object itself. For example, a plan with budget_id 101 and version_id 121 has a child program with budget_id 105. An allocation of 1000 is made to the plan and 500 to the child program in all of these quarters: 107, 108, 109, 110.In this example, the entries would be as follows.

Field Type Length Null? Description
budget_id INT32 false Budget ID of the object.
budget_alloc_version_id INT32 false Budget version to which this allocation belongs. This column is the foreign key reference to uap_budget_alloc_version, budget_alloc_version_id.
budget_period_id INT32 false The budget period for which this allocation is being made. This column is the foreign key reference to uap_budget_period, budget_period_id.
budget_amt FLOAT true Signals if the budget is self-assigned or comes from parent object.
alloc_amt FLOAT true Represents the allocation that is made by an object to its child. This value would always be 0 when budget_amt and line_item_amt are populated.
line_item_amt FLOAT true Represents the amount that an object reserves for its own usage out of its budgeted amount.

uap_tt_budget_rule

The table holds the budget rules that are defined in the object templates. This table was added in Unica Plan version 8.5.0 to support budget approvals.

Field Type Length Null? Description
template_id INT32 false The ID of the template in which the approval rule is defined.
rule_id INT32 false The ID of the rule that is created in the rules table.

uap_tt_me_metrics

This table holds the metrics created in the metric editor. It was created in version 8.5.0.

Field Type Length Null? Description
metric_id INT32 false The internal ID of the metric.
internal_name NVARCHAR 50 false The internal name of the metric.
display_key NVARCHAR 100 true The key of the display name of the metric used in the property file for localization.
display_name NVARCHAR 100 false The name of the metric entered by the user.
description_key NVARCHAR 100 true The description key of the metric used in the property file for localization.
description NVARCHAR 300 true The description of the metric entered by the user.
unit_type VARCHAR 20 false The data type of the metric.
display_format VARCHAR 5 false The display format for the value of the metric.
mprecision INT32 false The decimal precision of the value.

uap_tt_me_metrics_group

This table holds the metric groups created in the metric editor. It was created in version 8.5.0.

Field Type Length Null? Description
group_id INT32 false The internal ID of the group.
internal_name NVARCHAR 50 false The internal name of the group.
display_key NVARCHAR 100 true The display key of the group used in the property file for localization.
display_name NVARCHAR 100 false The name of the group entered by the user.
description_key NVARCHAR 100 true The description key of the group used in the property file for localization.
description NVARCHAR 300 true The description of the group entered by the user.

uap_tt_me_metrics_group_ref

This table holds the association of the groups with the template and the group display order. It was created in version 8.5.0.

Field Type Length Null? Description
template_id INT32 false The internal ID of the metric template.
group_id INT32 false The internal ID of the associated group.
tab_order INT32 The display order for the tabs in the template.

uap_tt_me_metrics_template

This table holds the metric templates created in the metric editor. It was created in Unica Plan version 8.5.0.

Field Type Length Null? Description
template_id INT32 false The internal ID of the metrics template.
internal_name NVARCHAR 50 false The internal name of the metrics template.
display_key NVARCHAR 100 true The key of the template display name used in the property file for localization.
display_name NVARCHAR 100 false The name of the metrics template entered by the user.
description_key NVARCHAR 100 true The description key of the template used in the property file for localization.
description NVARCHAR 300 true The description of the metrics template entered by the user.
type VARCHAR 20 false The type of the metrics template. The following are valid values.
  • 1 = PLAN
  • 2 = PROJECT
  • 8 = PROGRAM
  • -1 = LEGACY

uap_tt_me_metrics_dimension

This table holds metric dimensions created in the metric editor. It was created in version 8.5.0.

Field Type Length Null? Description
dimension_id INT32 false The internal ID of the metric dimension.
internal_name NVARCHAR 50 true The internal name of the metric dimension.
display_key NVARCHAR 100 true The key of the metric dimension used in the property file for localization.
display_name NVARCHAR 100 false The name of the metric dimension entered by the user.
description_key NVARCHAR 100 true The description key of the metric dimension used in the property file for localization.
description NVARCHAR 300 true The description of the metric dimension entered by the user.
type VARCHAR 20 false The type of the metric dimension. The following are valid values.
  • TARGET
  • ACTUAL
  • OTHER
tab_order INT32 The display order for the tabs in the metric dimension.

uap_tt_me_metrics_ref

This table holds association of the metric template groups. It was created in version 8.5.0.

Field Type Length Null? Description
template_id INT32 false The internal ID of the metric template.
group_id INT32 false The internal ID of the associated group.
metric_id INT32 false The internal ID of the metric associated with the group in the template.
is_formula INT32 false Specifies whether the field value is computed internally by a formula or by the user. The following are valid values.
  • 1 = computed by formula
  • 0 = user input
formula NVARCHAR 1000 true If is_formula is set to 1, then this column contains the actual formula.
is_planned INT32 false A flag to specify whether the metric is planned. The following are valid values.
  • 1 = Yes
  • 0 = No
is_rollup INT32 false A flag to specify whether the metric is rolled up. The following are valid values.
  • 1 = Yes
  • 0 = No
tab_order INT32 false The display order of the metrics within the group in the template.

uap_offer_attributes

This table holds the standard offer attributes associated with an offer created in Unica Plan. It was created in version 8.5.0.

Field Type Length Null? Description
mktg_object_id INT32 false The internal ID of the marketing object that is assigned to the offer.
campaign_sec_policy_id INT32 false The ID of the Unica Campaign security policy that is assigned to the offer.
campaign_sec_policy_name NVARCHAR 256 false The name of the Unica Campaign security policy that is assigned to the offer. Version 8.6 increased the size and permitted null values.
offer_code_1 NVARCHAR 40 true Offer code 1.
offer_code_2 NVARCHAR 40 true Offer code 2.
offer_code_3 NVARCHAR 40 true Offer code 3.
offer_code_4 NVARCHAR 40 true Offer code 4.
offer_code_5 NVARCHAR 40 true Offer code 5.
campaign_offer_id INT64 true The ID assigned to the offer by Unica Campaign.
is_approved INT32 true A flag which specifies whether the offer is approved.
is_offer_list NVARCHAR 1 true This column was added in version 8.6.0.
is_smart_list NVARCHAR 1 true This column was added in version 8.6.0.
partition_id NVARCHAR 256 true This column was added in version 8.6.0.
include_subfolder INT32 true This column was added in version 8.6.0.
offer_list_limit INT32 true This column was added in version 8.6.0.
cm_client_id VARCHAR 100 true The Digital Recommendations client ID associated with the offer. This column is the foreign key to uap_ml_cm_clients.cm_client_id. This column was added in version 9.0.
cm_category_id VARCHAR 200 true The Digital Recommendations category ID selected for the offer. This column was added in version 9.0.
cm_category_name NVARCHAR 200 true The Digital Recommendations category name that is selected for the offer. This column was added in version 9.0.

uap_offer_suppression

This table holds the offer suppression attributes associated with an offer created in Unica Plan. It was created in version 10.0.0

Field Type Length Null? Description
OfferID INT64 false The internal ID of the offer that is assigned to the offer.
SuppressionType INT32 false The type of suppression (ACCEPT=1, REJECT=2, CONTACT=3).
ResponseTypeCode NVARCHAR 64 true The code of the response type.
EventCount INT32 true Used only if the suppression type is CONTACT (else equals to 1).
AttributeID INT64 true The attribute ID.
StringValue NVARCHAR 1024 true The value of attribute (if the attribute is of type String).
NumberValue FLOAT true The value of attribute (if the attribute is of type Number).
DatetimeValue DATETIME true The value of attribute (if the attribute is of type Date).
SuppressionDuration INT32 true Number of day(s).

uap_tt_offer_template

This table holds the offer attributes associated with an offer template in Unica Plan. It was created in version 8.5.0.

Field Type Length Null? Description
template_id INT32 false This column specifies the offer template ID.
offer_code_format_1 NVARCHAR 32 true Offer code format 1.
offer_code_format_2 NVARCHAR 32 true Offer code format 2.
offer_code_format_3 NVARCHAR 32 true Offer code format 3.
offer_code_format_4 NVARCHAR 32 true Offer code format 4.
offer_code_format_5 NVARCHAR 32 true Offer code format 5.
treatment_code NVARCHAR 192 true The offer treatment code. In version 8.6.0, the size of this column increased.
treatment_code_gen_class NVARCHAR 750 true The treatment code generator for the offer template. In version 8.6.0, the size of this column increased.
template_published NVARCHAR 1 true A flag that specifies whether the template is published.
  • Y = yes
  • N = no
offer_code_pid_class NVARCHAR 750 true The offer code generator for the offer template. In version 8.6.0, the size of this column increased.
campaign_sec_policy_id INT64 The ID of the Unica Plan security policy that is assigned to the offer template.
campaign_sec_policy_name NVARCHAR 256 true The name of the Unica Plan security policy that is assigned to the offer template. In version 8.6.0, the size of this column increased.
campaign_template_id INT64 The ID assigned to the template in Unica Plan.
offer_list_template NVARCHAR 1 true This column was added in version 8.6.0.
coremetrics_integrated_flag INT32 true Indicates whether optional integration with Digital Recommendations is enabled for the offer template. This column was added in 9.0.
coremetrics_url VARCHAR 200 true The URL of an Digital Recommendations instance. Added in 9.0.

uap_version

Field Type Length Null? Description
current_version VARCHAR 20 false

uap_ui_tabset

Field Type Length Null? Description
tabset_id INT32 false
tabset_name NVARCHAR 30 true
object_type NVARCHAR 520 true
tabset_title NVARCHAR 60 true
is_visible VARCHAR 1 false Defaults to Y=Yes.

uap_ui_tabset_item

Field Type Length Null? Description
tabset_id INT32 false This column is the foreign key to uap_ui_tabset.
tabset_item_id INT32 false
tab_order INT32 true
item_name NVARCHAR 30 true
item_type NVARCHAR 20 true
xpos INT32 true
width INT32 true
height INT32 true
visible VARCHAR 1 true
divider VARCHAR 1 true
is_grid VARCHAR 1 true
selected VARCHAR 1 true
tab_group NVARCHAR 30 true
display NVARCHAR 200 true
icon_img NVARCHAR 200 true
img_id NVARCHAR 30 true
map_id NVARCHAR 30 true
display_id NVARCHAR 30 true
help_tip NVARCHAR 200 true
discriminator VARCHAR 1 false
is_visible VARCHAR 1 false Defaults to Y. The following values are valid.
  • Y = Yes
  • N = No

uap_ui_menu

Field Type Length Null? Description
menu_id INT32 false
menu_name NVARCHAR 30 true
object_type NVARCHAR 520 true
is_visible VARCHAR 1 false Defaults to Y. The following values are valid.
  • Y = Yes
  • N = No

uap_ui_menu_item

Field Type Length Null? Description
menu_id INT32 false This column is the foreign key to uap_ui_menu.
menu_item_id INT32 false
menu_item_name NVARCHAR 40 true
tab_order INT32 true
tab_id NVARCHAR 30 true
style NVARCHAR 200 true
display NVARCHAR 200 true
attribute_id NVARCHAR 30 true
is_grid VARCHAR 1 true
map_id NVARCHAR 30 true
item_type NVARCHAR 20 true
is_visible VARCHAR 1 false Defaults to Y. The following values are valid.
  • Y = Yes
  • N = No

uap_ui_column

Field Type Length Null? Description
column_id INT32 false
column_name NVARCHAR 40 true
display NVARCHAR 200 true
tooltip NVARCHAR 200 true
object_type NVARCHAR 520 true
is_visible VARCHAR 1 false Defaults to Y. The following values are valid.
  • Y = Yes
  • N = No

uap_list_filter

Field Type Length Null? Description
filter_id INT32 false
filter_item_name NVARCHAR 40 true
display NVARCHAR 200 true
item_value NVARCHAR 200 true
item_type VARCHAR 1 true The following values are valid.
  • T = Filter Text
  • A = Filter Attribute
object_type NVARCHAR 520 true
is_visible VARCHAR 1 false Defaults to Y. The following values are valid.
  • Y = Yes
  • N = No

uap_list_columns

Field Type Length Null? Description
column_list_id INT32 false
row_style INT32 true
suppress_chkbox VARCHAR 1 true
object_type NVARCHAR 520 true
is_visible VARCHAR 1 false Defaults to Y. The following values are valid.
  • Y = Yes
  • N = No

uap_list_column

Field Type Length Null? Description
column_list_id INT32 false This column is the foreign key to uap_list_columns.
column_id INT32 false
column_name NVARCHAR 40 true
tab_order INT32 true
column_type NVARCHAR 30 true
header_text NVARCHAR 200 true
help_tip NVARCHAR 200 true
style NVARCHAR 200 true
default_sort VARCHAR 1 true
is_visible VARCHAR 1 false Defaults to Y. The following values are valid.
  • Y = Yes
  • N = No

uap_offers_job_import

Field Type Length Null? Description
job_id INT32 false
partition_id NVARCHAR 256 true
start_date DATETIME true
end_date DATETIME true
total_offer_template_count INT32 true
total_offer_folder_count INT32 true
total_offer_count INT32 true
total_offer_list_count INT32 true
imported_offer_template_count INT32 true
imported_offer_folder_count INT32 true
imported_offer_count INT32 true
imported_offer_list_count INT32 true
updated_offer_count INT32 true
updated_offer_list_count INT32 true
status NVARCHAR 50 true
failure_reason NVARCHAR 1024 true

uap_offers_job_messages

This table was added in version 8.6.0.

Field Type Length Null? Description
job_id INT32 false
message_code NVARCHAR 50 true
message_level NVARCHAR 50 true
obj_type NVARCHAR 50 true
failed_object NVARCHAR 1024 true
dependent_type NVARCHAR 50 true
dependents CLOB true
message CLOB true

uap_offers_import

This table was added in version 8.6.0.

Field Type Length Null? Description
offer_import_id INT32 false
job_id INT32 false
partition_id NVARCHAR 256 false
comp_type NVARCHAR 50 false
campaign_id INT64 false
umo_id INT64 false
status NVARCHAR 20 true

uap_object_folder

This table stores the folder IDs for offer folders in systems integrated with Unica Campaign. This table was added in version 8.6.

Field Type Length Null? Description
folder_id INT64 false
campaign_folder_id INT64 true
folder_name NVARCHAR 765 false
description NVARCHAR 1024 true
parent_folder_id INT64 true
object_type INT32 true
campaign_sec_policy_id INT64 false
campaign_sec_policy_name NVARCHAR 256 false
partition_id NVARCHAR 256 false
created_date DATETIME true
created_by INT32 true
last_mod_date DATETIME true
modified_by INT32 true

uap_outofoffice_setting

Field Type Length Null? Description
outofoffice_setting_id INT32 false This column is the primary key for out of office delegation settings.
is_on INT32 false Defaults to 0.
user_id INT32 true User ID of the person setting out of office delegation.
workflow_delegate_user_id INT32 true User ID of the person delegated to handle workflow and approval tasks.
approval_delegate_user_id INT32 true User ID of the person delegated to handle stand-alone approvals and budget approvals.
request_delegate_user_id INT32 true User ID of the person delegated to handle project requests.
comments NVARCHAR 255 true User-defined text to send with delegated tasks.
status NVARCHAR 20 true
created_date DATETIME true Creation date.
modified_date DATETIME true Date of the last modification.
dg_prj_req INT32 false Delegate a project request: Either of the values 0,1 or 2 assigned for Do not auto-delegate, Auto-delegate all or Auto-delegate selected respectively.
dg_sta_appr_n_bdg_appr INT32 false Delegate standalone approvals and budget approvals:Either of the values 0,1 or 2 assigned for Do not auto-delegate, Auto-delegate all or Auto-delegate selected respectively.
dg_wf_tsk_n_appr INT32 false Delegate workflow tasks and approvals: Either of the values 0,1 or 2 assigned for Do not auto-delegate, Auto-delegate all or Auto-delegate selected respectively.

uap_offerlist_offer

This table was added in version 8.6.0.

Field Type Length Null? Description
mktg_object_id INT32 false
item_id INT64 false
seq_no INT32 false
search_criteria CLOB true

uap_tt_offer_attributes

This table was added in version 8.6.0.

Field Type Length Null? Description
umo_element_name NVARCHAR 100 false
campaign_internal_name NVARCHAR 100 false

uap_outofoffice_hist

Field Type Length Null? Description
history_id INT32 false
outofoffice_id INT32 false
owner_id INT32 false
deligated_user_id INT32 false
source_object_id INT32 false
source_object_type INT32 false
created_date DATETIME false

uap_ooo_autoadd_setting

Field Type Length Null? Description
ooo_autoadd_setting INT32 false System-wide setting for automated addition of out of office delegates to project teams.
modified_date DATETIME Last modification date of the setting.
modified_by INT32 User ID of the last person to change the setting.

uap_appr_deny_reasons

Stores items added to the Approval Deny Reasons defined list. For installations that require a reason, users select from this list when they deny an approval. This table was added in version 9.0.

Field Type Length Null? Description
deny_reason_id INT32 false Unique identifier that is generated by the system for each item in the list.
name NVARCHAR 255 true Label for the list item.
display NVARCHAR 1024 true Specifies how list items display: alphabetically with Name - Code or numerically with Code - Name.
code NVARCHAR 50 true Identifying code for the list item.
display_no INT32 true System-supplied number that is used to order the items in the list for display.
flag_enabled VARCHAR 1 true Indicates whether the list item is enabled and therefore displays as an option in the user interface. The following values are valid.
  • 0 = Disabled
  • 1 = Enabled

uap_tt_deny_reasons

For a project template, stores the specified subset of reasons that users can supply when they deny an approval. Added in version 9.0.

Field Type Length Null? Description
deny_reason_id INT32 false Identifies a reason that is valid for denying an approval from uap_appr_deny_reasons. deny_reason_id.
template_id INT32 false Identifies a template from uap_tt_templates.template_id.

uap_stdappr_deny_reasons

Stores the specified subset of reasons that users can supply when they deny a stand-alone approval. This table was added in version 9.0.

Field Type Length Null? Description
deny_reason_id INT32 false Identifies a reason that is valid for denying an approval from uap_appr_deny_reasons. deny_reason_id.

uap_ml_cm_clients

Stores items for the list of Coremetrics Clients that administrators can import when they enable integration with Digital Recommendations in offer templates. This table was added in version 9.0.

Field Type Length Null? Description
cm_client_id INT32 false Unique identifier that is generated by the system for each client in the list.
name NVARCHAR 255 false Label for the list item.
code NVARCHAR 50 true The client ID value from a Digital Recommendations installation.
display NVARCHAR 305 false Specifies how list items display: alphabetically with Name - Code or numerically with Code - Name.
display_no INT32 true System-supplied number that is used to order the items in the list for display.
flag_enabled VARCHAR 1 true Indicates whether the list item is enabled and included in the user interface or not. The following values are valid.
  • 0 = Disabled
  • 1 = Enabled

uap_tt_cm_clients

This table holds the Digital Recommendations client IDs associated with an offer template in Unica Plan. This table was added in version 9.0.

Field Type Length Null? Description
template_id INT32 false This column is the foreign key to uap_tt_templates.template_id. Identifies the offer template.
cm_client_id NVARCHAR 100 false This column is the foreign key to uap_ml_cm_clients.cm_client_id. Stores the Digital Recommendations client ID.

uap_map_table

This table stores the object names with the corresponding database table and key column information. This table was added in version 9.1.0. Prior to 9.1.0, this information was stored in the XML files.

Field Type Length Null? Description
map_id INT32 false The internal ID of the map.
object_name NVARCHAR 520 false The name of the object.
table_name NVARCHAR 30 false The database table of corresponding object.
table_key NVARCHAR 30 false The key column of the database table.
order_column NVARCHAR 30 true The default order column.

uap_file_mime_types

This table contains the map between file extension and associated file MIME type. This content is used to determine the MIME type of uploaded file.

Field Type Length Null? Description
file_ext NVARCHAR 10 false The file extension. This column is the primary key.
mime_type NVARCHAR 255 false The MIME type of the file who's extension is stored in file_ext.

uap_map_column

Field Type Length Null? Description
map_id INT32 false
column_id INT32 false
column_name NVARCHAR 40 false
column_element NVARCHAR 200 true
column_label NVARCHAR 200 true
column_message NVARCHAR 1024 true
column_type NVARCHAR 40 true
max_length INT32 true
db_type NVARCHAR 40 true
tab_order INT32 true
required VARCHAR 1 true
lookup_table NVARCHAR 40 true
lookup_id_column NVARCHAR 40 true
lookup_desc_column NVARCHAR 40 true
default_value NVARCHAR 200 true
readonly VARCHAR 1 true
seq_no INT32 false

uap_project_bpm_instance

This table was added in version 10.0.0.2

Field Type Length Null? Description
project_id INT32 false Unique ID of the project. This column is the primary key.
process_instance_id INT64 true
bpmnnotation CLOB true

uap_project_subprocess_count

This table was added in version 10.0.0.2

Field Type Length Null? Description
project_id INT32 false Unique ID of the project.
sub_process_id NVARCHAR 50 false This column is the primary key.
count INT32 false

uap_wfs_registration

This table was added in version 10.0.0.2

Field Type Length Null? Description
account_id INT64 false This column is the primary key.
org_id INT64 false
application_name NVARCHAR 100 false
private_key CLOB false
public_key CLOB false
customer_name NVARCHAR 100 false
contact_person NVARCHAR 100 false
email_address NVARCHAR 100 false
Table 1. uap_histgenids
Field Type Length Null? Description
history_table_name NVARCHAR 50 False Contains the name of the history table. For example, uap_proj_hist, uap_approval_hist , uap_outofoffice_hist etc for the given marketing object type.
history_table_index_col NVARCHAR 50 False Contains the name of the index column in the history table. for example, seq_no for uap_approval_hist .
mobj_entity_id INT32 False Contains the primary key of the marketing object. For example, uap_approval, uap_project, etc.
max_index INT32 False Contains the maximum value of the index column (pointed by second column) in the history table pointed (pointed by first column) for a given marketing object.
version INT32 False Version number of the records in the table.

uap_checklist

Field Type Length NULL? Description
checklist_id (primary key) INT False System generated id for newly created checklist.
name NVARCHAR 256 False Name of the checklist.
description NVARCHAR 512 True Description of the checklist
owner_id INT False The user who created the checklist.
create_date DATETIME False The time of creation.
last_mod_date DATETIME False The time of last modification.
checklist_priority INT False Priority of the checklist. Available values are: 1 for LOW (default), 2 for MEDIUM, and 3 for HIGH.
status INT 1 False The status of the checklist.
version INT False Used for optimistic locking in hibernate. Incremented whenever the record is updated.

uap_todo_item

Field Type Length NULL? Description
todo_item_id (primary key) INT False System generated id for newly created to-do item.
name NVARCHAR 256 False Name of the todo item.
description NVARCHAR 512 True Description of the to-do item.
owner_id INT False The user who created the to-do item.
due_date DATETIME True Optional. The due date or the date by which the to-do item should be complete.
item_priority INT False Priority of the to-do item. Available options are: 1 for LOW (default), 2 for MEDIUM, and 3 for HIGH.
create_date DATETIME False The time of creation.
last_mod_date DATETIME False The time of last modification.
status INT True The status of the to-do. Available options are: 0 for NEW (default), 1 for IN_PROGRESS, and 2 for COMPLETE.
completed_by INT True The user ID of the user who completed the to-do item.
version INT False Used for optimistic locking in hibernate. Incremented whenever the record is updated.

uap_checklist_user

Contains the information about the users of the checklist

Field Type Length NULL? Description
checklist_id (primary key) INT False
user_id (primary key) INT False
sequence_no INT True
checklist_perm (primary key) INT False Available options are: 0 for OWNER, 1 for EDITOR (default), and 2 for VIEWER.

uap_todo_item_user

Contains information about the association of to-do item and users. It contains the assignees of the to-do item

Field Type Length NULL? Description
todo_item_id (primary key) INT False
user_id (primary key) INT False
Note: Currently, a to-do can be assigned to only one user, even though the database supports multiple assignees.

uap_checklist_todo_item

Contains information about the association of the checklist and to-do item

Field Type Length NULL? Description
todo_item_id (primary key) INT False
checklist_id (primary key) INT False
Note: Currently, a to-do item cannot belong to multiple checklists, even if the database supports such a case.

uap_checklist_hist

audit trail for checklist

Field Type Length NULL? Description
checklist_id (primary key) INT False
seq_no (primary key) INT False History / audit record number
user_id INT False The concerned user for the event.
user_name NVARCHAR 256 False The user's name.
action INT False The action taken on the checklist. Available options are: 0 for CREATE, 1 for MODIFY, 2 for DELETE, 3 for LINK (checklist linked to a to-do item) and 4 for DELINK (to-do item removed or delinked from the checklist but not deleted from the system.)
comments CLOB Currently not used.
access_time DATETIME False Time of the event or action.

uap_checklist_update_info

Provides details about the checklist modification event

Field Type Length NULL? Description
checklist_id (primary key) INT False
seq_no (primary key) INT False
field_name (primary key) NVARCHAR 50 False Property or an attribute For example, name, viewers, editors, etc.

uap_checklist_link_info

Provides details about the checklist and to-do item link event

Field Type Length NULL? Description
checklist_id (primary key) INT False
seq_no (primary key) INT False
todo_item_name NVARCHAR 256 False

uap_checklist_delink_info

Provides details about the checklist and to-do item delink event

Field Type Length NULL? Description
checklist_id (primary key) INT False
seq_no (primary key) INT False
todo_item_name NVARCHAR 256 False

uap_checklist_delete_info

Provides details about the checklist delete event. This is useful to indicate the case when the checklist was shared with one or more users and is present on their dashboard and later, the owner of the checklist deleted it from the system there by having dangling or broken references on the dashboard of other users with whom this checklist was shared previously.

Field Type Length NULL? Description
checklist_id (primary key) INT False
seq_no (primary key) INT False
checklist_name NVARCHAR (256) False
checklist_priority INT False Default is 1.

uap_todo_item_hist

Audit trail for to-do.

Field Type Length NULL? Description
todo_item_id (primary key) INT False
seq_no (primary key) INT False History / audit record number
user_id INT False The concerned user for the event.
user_name NVARCHAR 256 False The user's name.
action INT False The action taken on the checklist. Available options are: 0 for CREATE, 1 for MODIFY, 2 for DELETE, 3 for LINK (checklist linked to a to-do item) and 4 for DELINK (to-do item removed or delinked from the checklist but not deleted from the system.)
comments CLOB Currently not used.
access_time DATETIME False Time of the event or action.

uap_todo_item_update_info

Provides details about the to-do item modification event.

Field Type Length NULL? Description
todo_item_id (primary key) INT False
seq_no (primary key) INT False
field_name (primary key) NVARCHAR 50 False Property or an attribute. For example, dueDate, assignees, name, etc.

uap_todo_item_link_info

Provides details about the todo item and checklist link event.

Field Type Length NULL? Description
todo_item_id (primary key) INT False
seq_no (primary key) INT False
checklist_name NVARCHAR 256 False

uap_todo_item_delink_info

Provides details about the todo item and checklist delink event.

Field Type Length NULL? Description
todo_item_id (primary key) INT False
seq_no (primary key) INT False
checklist_name NVARCHAR 256 False

uap_checklist_dashboard

Checklist dashboard

Field Type Length NULL? Description
cl_dashboard_id (primary key) INT False System generated identifier.
name NVARCHAR 1024 False Name not used currently since application supports only one dashboard per user.
owner_id INT False Owner of the dashboard.
max_items INT False Currently restricted to 10 by the application.
is_default INT False Value is 1 always. Currently application supports only one dashboard which is "default".
create_date DATETIME False Creation time.
last_mod_date DATETIME False Last modification time.
version INT False Used for optimistic locking in hibernate. Incremented whenever the record is updated.

uap_cl_dashboard_details

Information about the checklists in the dashboard.

Field Type Length NULL? Description
cl_dashboard_id (primary key) INT False
checklist_id (primary key) INT False
seq_no INT False Index of checklist in the dashboard starts from 1 to max items (which is 10 currently).s

uap_checklist_notify

Information about the notification of the checklist sharing event that is when a checklist is shared with other user in edit mode or view mode

Field Type Length NULL? Description
checklist_notify_id (primary key) INT False System generated ID.
checklist_id INT False
event INT False 0 is for ASSIGNED (default) and 1 is for UNASSIGNED.
user_id INT False The user with whom the checklist is shared.
event_date DATETIME False 0 is for NOT NULL, 1 is for EDIT MODE, and 2 is for VIEW MODE (default).
checklist_perm INT False

uap_flagged_todo_item

Information about the to-do items flagged by the given user

Field Type Length NULL? Description
todo_item_id (primary key) INT False
user_id (primary key) INT False

A record in this table indicates that a given user has flagged a given to-do, since this functionality is per user.

uap_project_journey

Association of the project with journey that is when a given project is linked to a given journey

Field Type Length NULL? Description
project_id INT False
journey_id INT False

uap_todo_item_notes

Captures notes added by a user to a To-do

Field Type Length NULL? Description
note_id (primary key) INT False
todo_item_id INT False
user_id INT False
note VARGRAPHICS 512 True
created_date TIMESTAMP False
last_modified_by INT
edited INT False Default is 0
last_mod_date TIMESTAMP False

uap_todo_item_reject_info

Captures details of why a user rejected an assigned To-do, if the user chooses to do so.

Field Type Length NULL? Description
todo_item_id (primary key) INT False
seq_no (primary key) INT False
reason CLOB
assignee_id INT False

uap_offer_hist_mig_status

Keeps track of the current execution of the offer/offerlist(s) history migration activity.

Field Type Length NULL? Description
offer_hist_mig_status_id (primary key) INT No
current_count INT No Number of offer(s) and offerlist(s) whose history is being migrated to COM in the current execution of the activity
total_count INT No Total number of offer(s) and offerlist(s) for which there is some history which needs to be migrated to COM in the current execution of the activity
status VARCHAR 20 No Will have one of the following values:
  • STARTED: a new record, with this status value, is added to this table when new migration activity is started
  • COMPLETED: updates to this value when the current execution is completed without any errors for any of the offer(s)/offerlist(s)
  • INCOMPLETE: updates to this value when the current execution could not migrate the history for some offer(s)/offerlist(s)
partition_id VARGRAPHIC 256 Partition ID of the user who is executing the current migration activity.

uap_offer_hist_mig_tracker

Keeps track of the index of the most recent history record, which has been migrated for a given offer(s)/offerlist(s).

Field Type Length NULL? Description
mktg_object_id (primary key) INT No Marketing Object ID which is a foreign key to uap_mktgobject table.
max_hist_index INT No Index of the most recent history record, which was migrated for a given offer/offerlist.

uap_obj_hist_mig_failed

Keeps track of the offer(s)/offerlist(s) for which the current execution of the migration activity was not completely successful.

Field Type Length NULL? Description
mktg_object_id (primary key) INT No Marketing Object ID which is a foreign key to uap_mktgobject table.
name VARGRAPHIC 390 No Name of the offer/offerlist.
partition_id VARGRAPHIC 256 Partition ID of the user who is executing the current migration activity.

uap_com_approval

Stores the approval containing the offer, or offerlist, attached using the offer picker widget of Unica Centralized Offer Management.

Entry is removed from this table if one, or all, offer/offerlist attachments are removed from this approval.

Field Type Length NULL? Description
approval_id (Foreign Key) INT No Foreign key to uap_approvals (approval_id)

uap_collab

Stores the collobaration channel details, like slack channel name and slack channel ID, for a given object type (like plan, program, projects, etc.) and for a given object ID (like plan ID, program ID, project ID, etc.)

Field Type Length NULL? Description
object_type INT No Object type of the event.
object_id INT No Unique ID of the object. The table to which this is a foreign key depends on the object type.
name VARGRAPHIC 128 No
collab_sys_id VARGRAPHIC 128 Collaboration channe ID
status INT No Status of integration of collaboration channel with the project.
  • 0 - disabled
  • 1 - enabled