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

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

 

Staffing profile ID for proposal

No

STAFFING_ PROFILE _ STATUS_CODE

Nullabl e

INT

Staffing profile status code for proposal.

Lookup_type=’ RSC - Staffing Profile Status’

No

STAFFING_ PROFILE _ STATUS_NAME

Nullabl e

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

Nullabl e

INT

Financial summary ID for proposal

No

FINANCIAL_ SUMMARY _ NAME

Nullabl e

VARCHA R(1300)

Financial summary name for proposal

No

FINANCIAL_ SUMMARY_ DESCRIPTION

Nullabl e

VARCHA R(1300)

Financial summary description for proposal

No

FINANCIAL_ SUMMARY_ CURRENCY_ CODE

Nullabl e

VARCHA R(40)

Financial summary currency code for proposal

No

APPROVED_ BUDGET_ID

Nullabl e

INT

Approved budget ID for proposal

No

APPROVED_ BUDGET_NAME

Nullabl e

VARCHA R(200)

Approved budget name for proposal

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

Nullabl e

DATE

The start date of this staffing profile

No

STAFFING_ PROFILE_ FINISH_DATE

Nullabl e

DATE

The finishing date of this staffing profile

 

No

STAFFING_ PROFILE_ REGIONS_ID

Nullabl e

INT

Region with which this staffing profile is associated with

No

STAFFING_ PROFILE_ REGION_NAME

Nullabl e

VARCHA R(100)

Region name with which this staffing profile is associated with

No

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_ KEY

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)

Indicates if this staffing profile should show as workload. Values can 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 project

Yes

REQUEST_ TYPE_NAME

Nullabl e

VARCHA R(80)

Provides request type name for this project

Yes

PROP_ BUSINESS_ UNIT_CODE

Nullabl e

VARCHA R(30)

Business unit code for proposal

No

PROP_ BUSINESS_ UNIT_MEANING

Nullabl e

VARCHA R(80)

Business unit meaning for proposal

No

PROP_ BUSINESS_ OBJECTIVE_ID

Nullabl e

INT

Business objective ID for proposal

No

PROP_ BUSINESS_ OBJECTIVE_ NAME

Nullabl e

VARCHA R(80)

Business objective name for proposal

No

PROP_ PROJECT_ CLASS_CODE

Nullabl e

VARCHA R(30)

Project class code for proposal

No

PROP_ PROJECT_ CLASS_ MEANING

Nullabl e

VARCHA R(80)

Project class meaning for proposal

No

PROP_ASSET_ CLASS_CODE

Nullabl e

VARCHA R(30)

Asset class code for proposal

No

PROP_ASSET_ CLASS_ MEANING

Nullabl e

VARCHA R(80)

Asset class meaning for proposal

No

PROP_RETURN_ON_ INVESTMENT

Nullabl e

NUMERI C

Return on investment for proposal

No

PROP_NET_ PRESENT_ VALUE

Nullabl e

NUMERI C

Net present value for proposal

No

PROP_CUSTOM_ FIELD_VALUE

Nullabl e

NUMERI C

Financial metric for proposal

No

PROP_VALUE_ RATING

Nullabl e

NUMERI C

Score for score domain of Value

No

PROP_RISK_ RATING

Nullabl e

NUMERI C

Score for score domain of Risk

No

PROP_TOTAL_ SCORE

Nullabl e

NUMERI C

Total score for proposal

No

PROP_ DISCOUNT_ RATE

Nullabl e

NUMERI C

Discount rate for proposal

No

PROP_PLAN_ START_DATE

Nullabl e

DATE

Period name of the planned start date for the proposal

No

PROP_PLAN_ FINISH_DATE

Nullabl e

DATE

Period name of the planned finish date for the proposal

No

PROP_ PROJECT_ID

Nullabl e

INT

Unique identifier for projects

 

No

PROP_ PROJECT_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_PROJECTS

 

No

PROP_ PROJECT_ ENTERPRISE_ KEY

Nullabl e

INT

Unique identifier for foreign key

 

No

PROP_ PROJECT_NAME

Nullabl e

VARCHA R(300)

Project name for proposal

No

PROP_ DEPENDENCIE S_CODE

Nullabl e

VARCHA R(500)

Dependency code for proposal

No

PROP_ DEPENDENCIE S_MEANING

Nullabl e

VARCHA R(2000)

Dependency meaning for proposal

No

PROP_ PROJECT_TYPE_ ID

Nullabl e

INT

Project type ID for proposal

Yes

PROP_ PROJECT_TYPE_ NAME

Nullabl e

VARCHA R(1500)

Project type name for proposal

Yes

PROP_REGION_ ID

Nullabl e

INT

Region ID for proposal

Yes

PROP_REGION_ NAME

Nullabl e

VARCHA R(1500)

Region name for proposal

Yes

PROP_ FINANCIAL_ SUMMARY_ID

Nullabl e

INT

ID of the proposal's financial summary

No

PROP_ FINANCIAL_ SUMMARY_ NAME

Nullabl e

VARCHA R(1500)

Name of the proposal's financial summary

No

PROP_ APPROVED_ SNAPSHOT_ID

Nullabl e

INT

ID of the financial summary snapshot

taken at the time when the proposal is

approved

No

PROP_ APPROVED_ SNAPSHOT_ NAME

Nullabl e

VARCHA R(1500)

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

No

PROP_ PORTFOLIO_ID

Nullabl e

INT

ID of the portfolio to which the proposal is linked

No

PROP_ PORTFOLIO_ NAME

Nullabl e

VARCHA R(1500)

Name of the portfolio to which the proposal is linked

No

PROP_ PROGRAM_ID

Nullabl e

VARCHA R(4000)

IDs of the program containing this proposal

No

PROP_ PROGRAM_ NAME

Nullabl e

VARCHA R(4000)

Names of the programs containing this proposal

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

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