DIM_ASSETS

This table stores PFM asset information.

Foreign Keys

Primary Key Table	Primary Key Column	Foreign Key Column
DIM_RESOURCE_POOLS	RESOURCE_POOL_ID	PK_RESOURCE_POOLS_ID
DIM_PROJECTS	PROJECT_ID	PK_PROJECTS_ID

Column Descriptions

Column Name	Null?	Data Type	Description	SCD 2
PK_ASSETS_ID	Not Null	INT	System-generated identifier	No
MD_ ENTERPRISE_KEY	Nullable	VARCHAR(1000)	Primary key that is mapped to one or more source columns	No
MD_BUSINESS_KEY	Nullable	INT	Unique identifier for entity	No
STAFFING_PROFILE_ID	Nullable	INT	Staffing profile ID for assets	No
STAFFING_PROFILE_STATUS_CODE	Nullable	INT	Staffing profile status code for assets. Lookup_type=’ RSC - Staffing Profile Status’	No
STAFFING_PROFILE_STATUS_NAME	Nullable	VARCHAR(150)	Staffing profile status name for assets	No
STAFFING_PROFILE_NAME	Not Null	VARCHAR(300)	Staffing profile name for assets	No
FINANCIAL_SUMMARY_ID	Nullable	INT	Financial summary ID for assets	No
FINANCIAL_SUMMARY_NAME	Nullable	VARCHAR(1300)	Financial summary name for assets	No
FINANCIAL_SUMMARY_DESCRIPTION	Nullable	VARCHAR(1300)	Financial summary description for assets	No
FINANCIAL_SUMMARY_CURRENCY_CODE	Nullable	VARCHAR(40)	Financial summary currency code for assets	No
APPROVED_BUDGET_ID	Nullable	INT	Approved budget ID for assets	No
APPROVED_BUDGET_NAME	Nullable	VARCHAR(200)	Approved budget name for assets	No
APPROVED_BUDGET_EXPENSE_TYPE_CODE	Nullable	VARCHAR(40)	Expense type for which the budget is approved. Possible values are: CAPITAL, OPERATING.	No
APPROVED_BUDGET_AMOUNT_LCL	Nullable	NUMERIC	Amount of approved budget in local currency	No
APPROVED_BUDGET_AMOUNT_BSE	Nullable	NUMERIC	Amount of approved budget in base currency	No
APPROVED_BUDGET_ NOTES	Nullable	VARCHAR(1000)	Notes of approved budget	No
APPROVED_BUDGET_DATE	Nullable	DATE	Date at which the budget is approved	No
IS_SYNCH_BUDGET	Nullable	VARCHAR(1)	If the approved budget is synchronized, it describes the source. If the approved budget is not synchronized, the value is `N`; for example, synch_source_flag can be `FSR` for Financial Summary Rollup.	No
STAFFING_PROFILE_DESCRIPTION	Nullable	VARCHAR(650)	Indicates the description of the staffing profile	No
STAFFING_PROFILE_USER_DATA1-20	Nullable	VARCHAR(255)	User data segment	No
STAFFING_PROFILE_VISIBLE_USER_DATA1-20	Nullable	VARCHAR(255)	User data segment	No
DEFAULT_VIEW_EFFORT_TYPE_CODE	Nullable	INT	User preferred effort type (FTE, month, person days) to view position's demand. 0: FTE; 1:HOURS; 2:PERSON_DAYS	No
DEFAULT_VIEW_EFFORT_TYPE_NAME	Nullable	VARCHAR(20)	DECODE (DEFAULT_VIEW_EFFORT_TYPE_ CODE; 0: FTE; 1: HOURS; 2: PERSON_ DAYS; UNKNOWN)	No
IS_ACTIVE_FLAG	Nullable	VARCHAR(1)	Used to say if the staffing profile is active or not. Used mainly when a baseline is created and the flag is set to `N`.	Yes
STAFFING_PROFILE_START_DATE	Nullable	DATE	The start date of this staffing profile	No
STAFFING_PROFILE_FINISH_DATE	Nullable	DATE	The finish date of this staffing profile	No
STAFFING_PROFILE_REGIONS_ID	Nullable	INT	Region with which this staffing profile is associated with	Yes
STAFFING_PROFILE_REGION_NAME	Nullable	VARCHAR(100)	Region name with which this staffing profile is associated with	Yes
RESOURCCE_POOL_ID	Nullable	INT	Unique identifier for resource pool	Yes
RESOURCE_POOL_BUSINESS_KEY	Nullable	VARCHAR(1000)	Foreign key that is mapped to DIM_RESOURCE_POOLS	Yes
RESOURCE_POOL_ENTERPRISE_ KEYS	Nullable	INT	Unique identifier for foreign key	Yes
RESOURCE_POOL_NAME	Nullable	VARCHAR(260)	The resource pool associated to this staffing profile. Positions inherit this resource pool by default but can be overridden.	Yes
IS_CONSIDERED_WORKLOAD_FLAG	Nullable	VARCHAR(1)	A flag to indicate if this staffing profile should show as workload. Values could be `Y` or `N`.	Yes
WORK_LOAD_CATEGORY_CODE	Nullable	VARCHAR(255)	Indicates what this staffing profile is used for - Strategic Projects, Legacy systems, and so on.	Yes
CONTAINER_ENTITY_ID	Nullable	INT	Container entity ID related to this table	No
CONTAINER_ENTITY_TYPE_CODE	Nullable	INT	Specifies if this staffing profile is for project, ORG_ UNIT, asset, or freestanding. The entity type is decoded by the application.	No
DEF_VIEW_PERIOD_TYPE	Nullable	INT	Indicates the default period type while viewing the staffing profile. 8: WEEK 4: MONTH 5: QUARTER 7: HALF_YEAR 6: YEAR	No
DEF_VIEW_PERIOD_TYPE_NAME	Nullable	VARCHAR(10)	DECODE (DEF_VIEW_PERIOD_ TYPE; 4: MONTH; 5: QUARTER; 6: YEAR; 7: HALF_YEAR; 8: WEEK; UNKNOWN)	No
REQUEST_TYPE_ID	Nullable	INT	Provides request type ID for this asset	Yes
REQUEST_NAME	Nullable	VARCHAR(80)	Provides request type name for this asset	Yes
ASSET_NAME	Nullable	VARCHA R(300)	Name for the asset	No
ASSET_HEALTH_CODE	Nullable	VARCHAR(30)	Health information for asset	No
ASSET_HEALTH_MEANING	Nullable	VARCHAR(80)	Health meaning for asset	No
ASSET_BUSINESS_UNIT_CODE	Nullable	VARCHAR(30)	Business unit meaning for asset	No
ASSET_BUSINESS_UNIT_MEANING	Nullable	VARCHA R(80)	Business unit meaning for asset	No
ASSET_BUSINESS_OBJECTIVE_ID	Nullable	INT	Business objective ID for asset	No
ASSET_BUSINESS_OBJECTIVE_NAME	Nullable	VARCHAR(80)	Business objective name for asset	No
ASSET_PROJECT_CLASS_CODE	Nullable	VARCHA R(30)	Project class code for asset	No
ASSET_PROJECT_CLASS_MEANING	Nullable	VARCHA R(80)	Project class meaning for asset	No
ASSET_ASSET_CLASS_CODE	Nullable	VARCHAR(30)	Asset class code for asset	No
ASSET_ASSET_CLASS_MEANING	Nullable	VARCHA R(80)	Asset class meaning for asset	No
ASSET_PROJECT_ID	Nullable	INT	Unique identifier for project	No
ASSET_PROJECT_BUSINESS_KEY	Nullable	VARCHAR(1000)	Foreign key that is mapped to DIM_ PROJECTS	No
ASSET_PROJECT_ENTERPRISE_KEY	Nullable	INT	Unique identifier for foreign key	No
ASSET_PROJECT_NAME	Nullable	VARCHAR	Project name for asset	No
ASSET_PROJECT_URL	Nullable	VARCHAR(250)	Project URL for asset	No
ASSET_RETURN_ON_INVESTMENT	Nullable	NUMERIC	ROI for asset	No
ASSET_NET_PRESENT_VALUE	Nullable	NUMERIC	NPV for asset	No
ASSET_CUSTOM_FIELD_VALUE	Nullable	NUMERIC	Custom financial metric value for asset	No
ASSET_VALUE_RATING	Nullable	NUMERIC	Value for score of score domain `Value`	No
ASSET_RISK_RATING	Nullable	NUMERIC	Value for score of score domain `Risk`	No
ASSET_TOTAL_SCORE	Nullable	NUMERIC	Total score for asset	No
ASSET_DISCOUNT_RATE	Nullable	NUMERIC	Discount rate for asset	No
ASSET_DEPENDENCIES_CODE	Nullable	VARCHAR(500)	Asset dependency code for asset	No
ASSET_DEPENDENCIES_MEANING	Nullable	VARCHAR(2000)	Asset dependency meaning for asset	No
ASSET_REGION_ ID	Nullable	INT	Region ID for asset	Yes
ASSET_REGION_ NAME	Nullable	VARCHAR(1500)	Region name for asset	Yes
ASSET_ FINANCIAL_ SUMMARY_ID	Nullable	INT	Financial summary ID for the asset	No
ASSET_ FINANCIAL_ SUMMARY_ NAME	Nullable	VARCHAR(1500)	Financial summary name for the asset	No
ASSET_ PORTFOLIO_ID	Nullable	INT	Portfolio ID for the asset	No
ASSET_ PORTFOLIO_ NAME	Nullable	VARCHAR(1500)	Portfolio name for the asset	No
ASSET_ PROGRAM_ID	Nullable	VARCHAR(4000)	IDs of the programs containing this asset	No
ASSET_ PROGRAM_ NAME	Nullable	VARCHAR(4000)	Names of the programs containing this asset	No
MD_BATCH_ID	Nullable	INT	ID of data batch	No
MD_PROCESS_ ID	Nullable	INT	ID of ETL process running on a specific task	No
MD_SOURCE_ INSTANCE_ID	Nullable	INT	Content pack ID of source instance	No
MD_ CREATEDDATE	Nullable	DATETIME	Date of the record being inserted	No
MD_ DELETEDDATE	Nullable	DATETIME	Date of the record being deleted	No
MD_ LASTMODDATE	Nullable	DATETIME	Date of the record being updated	No
MD_ TRANSLASTIND	Nullable	VARCHAR(1)	Flags whether or not there is SCD2 last record	No
MD_ TRANSENDDATE	Nullable	DATETIME	End date of the record being effective	No
MD_ ACTIVESTATUSI ND	Nullable	VARCHAR(1)	Flags whether or not the old record is deleted	No
MD_HASH_ CODE_SCD1	Nullable	VARCHAR(40)	(Only for dimension table) Hash code that is generated by SCD1 columns	No
MD_HASH_ CODE_SCD2	Nullable	VARCHAR(40)	(Only for dimension table) Hash code that is generated by SCD2 columns	No

Projections

This table uses no projections.

Sequences

Sequence Name	Sequence Type
ASSETS_ENTERPRISE_XREF_SEQ	MD_ENTERPRISE_KEY
ASSETS_KEY_LOOKUP_SEQ	PK_ ASSETS_ID
RESOURCE_POOLS _ENTERPRISE_XREF_SEQ	RESOURCE_POOL_ENTERPRISE_KEY
RESOURCE_POOLS _KEY_LOOKUP_SEQ	RESOURCE_POOL_ID
PROJECTS_ENTERPRISE_XREF_SEQ	PROJECT_ENTERPRISE_KEY
PROJECTS_KEY_LOOKUP_SEQ	PROJECT_ID