DIM_PROPOSALS

This table stores PFM proposals 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_PROPOSALS_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

 

Staffing profile ID for proposal

No

STAFFING_PROFILE_STATUS_CODE

Nullable

INT

Staffing profile status code for proposal.

Lookup_type=’ RSC - Staffing Profile Status’

No

STAFFING_PROFILE_STATUS_NAME

Nullable

VARCHA R(150)

Staffing profile status name for proposal

 

No

STAFFING_PROFILE_NAME

Not Null

VARCHA R(300)

Staffing profile name for proposal

 

No

FINANCIAL_SUMMARY_ID

Nullable

INT

Financial summary ID for proposal

No

FINANCIAL_SUMMARY_NAME

Nullable

VARCHAR(1300)

Financial summary name for proposal

No

FINANCIAL_SUMMARY_DESCRIPTION

Nullable

VARCHA R(1300)

Financial summary description for proposal

No

FINANCIAL_SUMMARY_CURRENCY_CODE

Nullable

VARCHA R(40)

Financial summary currency code for proposal

No

APPROVED_BUDGET_ID

Nullable

INT

Approved budget ID for proposal

No

APPROVED_BUDGET_NAME

Nullable

VARCHAR(200)

Approved budget name for proposal

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 flag if the staffing profile is active or not. Used mainly when a baseline is created and the flag is set to N.

No

STAFFING_ PROFILE_ START_DATE

Nullable

DATE

The start date of this staffing profile

No

STAFFING_PROFILE_FINISH_DATE

Nullable

DATE

The finishing date of this staffing profile

 

No

STAFFING_PROFILE_REGIONS_ID

Nullable

INT

Region with which this staffing profile is associated with

No

STAFFING_PROFILE_REGION_NAME

Nullable

VARCHA R(100)

Region name with which this staffing profile is associated with

No

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_KEY

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)

Indicates if this staffing profile should show as workload. Values can be Y or N.

Yes

WORK_LOAD_CATEGORY_CODE

Nullable

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

Yes

REQUEST_TYPE_NAME

Nullable

VARCHAR(80)

Provides request type name for this project

Yes

PROP_BUSINESS_UNIT_CODE

Nullable

VARCHAR(30)

Business unit code for proposal

No

PROP_BUSINESS_UNIT_MEANING

Nullable

VARCHA R(80)

Business unit meaning for proposal

No

PROP_BUSINESS_OBJECTIVE_ID

Nullable

INT

Business objective ID for proposal

No

PROP_BUSINESS_OBJECTIVE_NAME

Nullable

VARCHA R(80)

Business objective name for proposal

No

PROP_PROJECT_CLASS_CODE

Nullable

VARCHAR(30)

Project class code for proposal

No

PROP_PROJECT_CLASS_MEANING

Nullable

VARCHAR(80)

Project class meaning for proposal

No

PROP_ASSET_CLASS_CODE

Nullable

VARCHAR(30)

Asset class code for proposal

No

PROP_ASSET_CLASS_MEANING

Nullable

VARCHAR(80)

Asset class meaning for proposal

No

PROP_RETURN_ON_ INVESTMENT

Nullable

NUMERIC

Return on investment for proposal

No

PROP_NET_PRESENT_ VALUE

Nullable

NUMERIC

Net present value for proposal

No

PROP_CUSTOM_ FIELD_VALUE

Nullable

NUMERIC

Financial metric for proposal

No

PROP_VALUE_ RATING

Nullable

NUMERIC

Score for score domain of Value

No

PROP_RISK_RATING

Nullable

NUMERIC

Score for score domain of Risk

No

PROP_TOTAL_ SCORE

Nullable

NUMERIC

Total score for proposal

No

PROP_DISCOUNT_ RATE

Nullable

NUMERIC

Discount rate for proposal

No

PROP_PLAN_ START_DATE

Nullable

DATE

Period name of the planned start date for the proposal

No

PROP_PLAN_FINISH_DATE

Nullable

DATE

Period name of the planned finish date for the proposal

No

PROP_PROJECT_ID

Nullable

INT

Unique identifier for projects

 

No

PROP_PROJECT_ BUSINESS_KEY

Nullable

VARCHA R(1000)

Foreign key that is mapped to DIM_PROJECTS

 

No

PROP_ PROJECT_ENTERPRISE_ KEY

Nullable

INT

Unique identifier for foreign key

 

No

PROP_PROJECT_NAME

Nullable

VARCHA R(300)

Project name for proposal

No

PROP_DEPENDENCIE S_CODE

Nullable

VARCHAR(500)

Dependency code for proposal

No

PROP_ DEPENDENCIE S_MEANING

Nullable

VARCHAR(2000)

Dependency meaning for proposal

No

PROP_ PROJECT_TYPE_ ID

Nullable

INT

Project type ID for proposal

Yes

PROP_ PROJECT_TYPE_ NAME

Nullable

VARCHAR(1500)

Project type name for proposal

Yes

PROP_REGION_ ID

Nullable

INT

Region ID for proposal

Yes

PROP_REGION_ NAME

Nullable

VARCHAR(1500)

Region name for proposal

Yes

PROP_FINANCIAL_ SUMMARY_ID

Nullable

INT

ID of the proposal's financial summary

No

PROP_ FINANCIAL_ SUMMARY_ NAME

Nullable

VARCHA R(1500)

Name of the proposal's financial summary

No

PROP_APPROVED_ SNAPSHOT_ID

Nullable

INT

ID of the financial summary snapshot

taken at the time when the proposal is

approved

No

PROP_ APPROVED_ SNAPSHOT_NAME

Nullable

VARCHAR(1500)

Name of the financial summary snapshot taken at the time when the proposal is approved

No

PROP_PORTFOLIO_ID

Nullable

INT

ID of the portfolio to which the proposal is linked

No

PROP_ PORTFOLIO_ NAME

Nullable

VARCHA R(1500)

Name of the portfolio to which the proposal is linked

No

PROP_ PROGRAM_ID

Nullable

VARCHA R(4000)

IDs of the program containing this proposal

No

PROP_ PROGRAM_ NAME

Nullable

VARCHA R(4000)

Names of the programs containing this proposal

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

PROPOSALS_ENTERPRISE_XREF_SEQ

MD_ENTERPRISE_KEY

PROPOSALS _KEY_LOOKUP_SEQ

PK_ PROPOSALS_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