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
|
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 |
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 |
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 |
No |
PROP_RISK_RATING |
Nullable |
NUMERIC |
Score
for score domain
of |
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 |