Share this page
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 |
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 |
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 |
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 |
No |
|
ASSET_RISK_RATING |
Nullable |
NUMERIC |
Value
for score of score
domain |
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 |
