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

Nullabl e

VARCHA R(1000)

Primary key that is mapped to one or more source columns

 

No

MD_BUSINESS_ KEY

Nullabl e

INT

Unique identifier for entity

No

STAFFING_ PROFILE_ID

Nullabl e

INT

Staffing profile ID for assets

No

STAFFING_ PROFILE _ STATUS_CODE

Nullabl e

INT

Staffing profile status code for assets.

Lookup_type=’ RSC - Staffing Profile Status’

No

STAFFING_ PROFILE _ STATUS_NAME

Nullabl e

VARCHA R(150)

Staffing profile status name for assets

 

No

STAFFING_ PROFILE_NAME

Not Null

VARCHA R(300)

Staffing profile name for assets

 

No

FINANCIAL_ SUMMARY_ID

Nullabl e

INT

Financial summary ID for assets

No

FINANCIAL_ SUMMARY_ NAME

Nullabl e

VARCHA R(1300)

Financial summary name for assets

No

FINANCIAL_ SUMMARY_ DESCRIPTION

Nullabl e

VARCHA R(1300)

Financial summary description for assets

No

FINANCIAL_ SUMMARY_ CURRENCY_ CODE

Nullabl e

VARCHA R(40)

Financial summary currency code for assets

No

APPROVED_ BUDGET_ID

Nullabl e

INT

Approved budget ID for assets

No

APPROVED_ BUDGET_NAME

Nullabl e

VARCHA R(200)

Approved budget name for assets

No

APPROVED_ BUDGET_ EXPENSE_ TYPE_CODE

Nullabl e

VARCHA R(40)

Expense type for which the budget is approved. Possible values are: CAPITAL, OPERATING.

No

APPROVED_ BUDGET_ AMOUNT_LCL

Nullabl e

NUMERI C

Amount of approved budget in local currency

No

APPROVED_ BUDGET_ AMOUNT_BSE

Nullabl e

NUMERI C

Amount of approved budget in base currency

No

APPROVED_ BUDGET_ NOTES

Nullabl e

VARCHA R(1000)

Notes of approved budget

No

APPROVED_ BUDGET_DATE

Nullabl e

DATE

Date at which the budget is approved

No

IS_SYNCH_ BUDGET

Nullabl e

VARCHA R(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

Nullabl e

VARCHA R(650)

Indicates the description of the staffing profile

No

STAFFING_ PROFILE_USER_ DATA1-20

Nullabl e

VARCHA R(255)

User data segment

No

STAFFING_ PROFILE_ VISIBLE_USER_ DATA1-20

Nullabl e

VARCHA R(255)

User data segment

No

DEFAULT_VIEW_ EFFORT_TYPE_ CODE

Nullabl e

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

Nullabl e

VARCHA R(20)

DECODE (DEFAULT_VIEW_EFFORT_TYPE_ CODE; 0: FTE; 1: HOURS; 2: PERSON_ DAYS; UNKNOWN)

No

IS_ACTIVE_FLAG

Nullabl e

VARCHA R(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

Nullabl e

DATE

The start date of this staffing profile

No

STAFFING_ PROFILE _ FINISH_DATE

Nullabl e

DATE

The finish date of this staffing profile

 

No

STAFFING_ PROFILE_ REGIONS_ID

Nullabl e

INT

Region with which this staffing profile is associated with

Yes

STAFFING_ PROFILE_ REGION_NAME

Nullabl e

VARCHA R(100)

Region name with which this staffing profile is associated with

Yes

RESOURCCE_ POOL_ID

Nullabl e

INT

Unique identifier for resource pool

Yes

RESOURCE_ POOL_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_RESOURCE_POOLS

 

Yes

RESOURCE_ POOL_ ENTERPRISE_ KEYS

Nullabl e

INT

Unique identifier for foreign key

 

Yes

RESOURCE_ POOL_NAME

Nullabl e

VARCHA R(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

Nullabl e

VARCHA R(1)

A flag to indicate if this staffing profile should show as workload. Values could be Y or N.

Yes

WORK_LOAD_ CATEGORY_ CODE

Nullabl e

VARCHA R(255)

Indicates what this staffing profile is used for - Strategic Projects, Legacy systems, and so on.

Yes

CONTAINER_ ENTITY_ID

Nullabl e

INT

Container entity ID related to this table

 

No

CONTAINER_ ENTITY_TYPE_ CODE

Nullabl e

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

Nullabl e

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

Nullabl e

VARCHA R(10)

DECODE (DEF_VIEW_PERIOD_ TYPE; 4: MONTH; 5: QUARTER; 6: YEAR; 7: HALF_YEAR; 8: WEEK; UNKNOWN)

No

REQUEST_ TYPE_ID

Nullabl e

INT

Provides request type ID for this asset

Yes

REQUEST_NAME

Nullabl e

VARCHA R(80)

Provides request type name for this asset

Yes

ASSET_NAME

Nullabl e

VARCHA R(300)

Name for the asset

No

ASSET_HEALTH_ CODE

Nullabl e

VARCHA R(30)

Health information for asset

No

ASSET_HEALTH_ MEANING

Nullabl e

VARCHA R(80)

Health meaning for asset

No

ASSET_ BUSINESS_ UNIT_CODE

Nullabl e

VARCHA R(30)

Business unit meaning for asset

No

ASSET_ BUSINESS_ UNIT_MEANING

Nullabl e

VARCHA R(80)

Business unit meaning for asset

No

ASSET_ BUSINESS_ OBJECTIVE_ID

Nullabl e

INT

Business objective ID for asset

No

ASSET_ BUSINESS_ OBJECTIVE_ NAME

Nullabl e

VARCHA R(80)

Business objective name for asset

No

ASSET_ PROJECT_ CLASS_CODE

Nullabl e

VARCHA R(30)

Project class code for asset

No

ASSET_ PROJECT_ CLASS_ MEANING

Nullabl e

VARCHA R(80)

Project class meaning for asset

No

ASSET_ASSET_ CLASS_CODE

Nullabl e

VARCHA R(30)

Asset class code for asset

No

ASSET_ASSET_ CLASS_ MEANING

Nullabl e

VARCHA R(80)

Asset class meaning for asset

No

ASSET_ PROJECT_ID

Nullabl e

INT

Unique identifier for project

No

ASSET_ PROJECT_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_ PROJECTS

 

No

ASSET_ PROJECT_ ENTERPRISE_ KEY

Nullabl e

INT

Unique identifier for foreign key

 

No

ASSET_ PROJECT_NAME

Nullabl e

VARCHA R(300)

Project name for asset

No

ASSET_ PROJECT_URL

Nullabl e

VARCHA R(250)

Project URL for asset

No

ASSET_ RETURN_ON_ INVESTMENT

Nullabl e

NUMERI C

ROI for asset

No

ASSET_NET_ PRESENT_ VALUE

Nullabl e

NUMERI C

NPV for asset

No

ASSET_ CUSTOM_ FIELD_VALUE

Nullabl e

NUMERI C

Custom financial metric

value for asset

No

ASSET_VALUE_ RATING

Nullabl e

NUMERI C

Value for score of score domain Value

No

ASSET_RISK_ RATING

Nullabl e

NUMERI C

Value for score of score domain Risk

No

ASSET_TOTAL_ SCORE

Nullabl e

NUMERI C

Total score for asset

No

ASSET_ DISCOUNT_ RATE

Nullabl e

NUMERI C

Discount rate for asset

No

ASSET_ DEPENDENCIE S_CODE

Nullabl e

VARCHA R(500)

Asset dependency code for asset

No

ASSET_ DEPENDENCIE S_MEANING

Nullabl e

VARCHA R(2000)

Asset dependency meaning for asset

No

ASSET_REGION_ ID

Nullabl e

INT

Region ID for asset

Yes

ASSET_REGION_ NAME

Nullabl e

VARCHA R(1500)

Region name for asset

Yes

ASSET_ FINANCIAL_ SUMMARY_ID

Nullabl e

INT

Financial summary ID for the asset

No

ASSET_ FINANCIAL_ SUMMARY_ NAME

Nullabl e

VARCHA R(1500)

Financial summary name for the asset

No

ASSET_ PORTFOLIO_ID

Nullabl e

INT

Portfolio ID for the asset

No

ASSET_ PORTFOLIO_ NAME

Nullabl e

VARCHA R(1500)

Portfolio name for the asset

No

ASSET_ PROGRAM_ID

Nullabl e

VARCHA R(4000)

IDs of the programs containing this asset

No

ASSET_ PROGRAM_ NAME

Nullabl e

VARCHA R(4000)

Names of the programs containing this asset

No

MD_BATCH_ID

Nullabl e

INT

ID of data batch

No

MD_PROCESS_ ID

Nullabl e

INT

ID of ETL process running on a specific task

No

MD_SOURCE_ INSTANCE_ID

Nullabl e

INT

Contnet pack ID of source instance

No

MD_ CREATEDDATE

Nullabl e

DATETIM E

Date of the record being inserted

No

MD_ DELETEDDATE

Nullabl e

DATETIM E

Date of the record being deleted

No

MD_ LASTMODDATE

Nullabl e

DATETIM E

Date of the record being updated

No

MD_ TRANSLASTIND

Nullabl e

VARCHA R(1)

Flags whether or not there is SCD2 last record

No

MD_ TRANSENDDATE

Nullabl e

DATETIM E

End date of the record being effective

No

MD_ ACTIVESTATUSI ND

Nullabl e

VARCHA R(1)

Flags whether or not the old record is deleted

No

MD_HASH_ CODE_SCD1

Nullabl e

VARCHA R(40)

(Only for dimension table) Hash code that is generated by SCD1 columns

No

MD_HASH_ CODE_SCD2

Nullabl e

VARCHA R(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