DIM_PROJECTS

This table stores the PPM project entity information.

Foreign Keys

Primary Key Table

Primary Key Column

Foreign Key Column

DIM_RESOURCE_POOLS

RESOURCE_POOL_ID

PK_ RESOURCE_POOLS _ID

DIM_REQUESTS

REQUEST_ID

PK_REQUESTS_ID

DIM_CONTACTS

CONTACT_ID

PK_CONTACTS_ID

Column Descriptions

 Column Name  Null? Data Type  Description SCD 2

PK_PROJECTS_ ID

Nullabl e

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

No

STAFFING_ PROFILE _ STATUS_CODE

Nullabl e

INT

Indicates the status of the staffing profile that is decoded by the application. Examples of status_code includes In Planning, Active, Completed, and so on.

No

STAFFING_ PROFILE _ STATUS_NAME

Nullabl e

VARCHA R(150)

Indicates the status name of the staffing profile

No

STAFFING_ PROFILE_NAME

Not Null

VARCHA R(300)

User entered name for this staffing profile

No

FINANCIAL_ SUMMARY_ID

Nullabl e

INT

Financial summary ID

No

FINANCIAL_ SUMMARY _ NAME

Nullabl e

VARCHA R(1300)

Name of the financial summary

No

FINANCIAL_ SUMMARY_ DESCRIPTION

Nullabl e

VARCHA R(1300)

Description of the financial summary

No

FINANCIAL_ SUMMARY_ CURRENCY_ CODE

Nullabl e

VARCHA R(40)

Currency code that indicates the local currency used for the financial summary

No

APPROVED_ BUDGET_ID

Nullabl e

INT

Identifier of financial summary to which the approved budget belongs

No

APPROVED_ BUDGET_NAME

Nullabl e

VARCHA R(200)

Name of the approved budget

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

Yes

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

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

PROJECT_ REGION_ID

Nullabl e

INT

Provides region information for this project

Yes

PROJECT_ REGION_NAME

Nullabl e

VARCHA R(100)

Provides region name information for this project

Yes

ROLLUP_ID

Nullabl e

INT

Provides foreign key reference to the PM_PROJECT_ROLLUP table

No

SCHEDULE_ HEALTH_ INDICATOR

Nullabl e

VARCHA R(255)

Stores project schedule health information

No

OVERRIDE_ DESCRIPTION

Nullabl e

VARCHA R(750)

Stores the description for overriding the project health

No

OVERRIDE_ DATE

Nullabl e

DATE

Stores date the project health was overridden

No

OVERALL_ HEALTH_ INDICATOR

Nullabl e

VARCHA R(255)

Stores the overall health information for the project

No

COST_HEALTH_ INDICATOR

Nullabl e

VARCHA R(255)

Stores the cost health information for the project

No

BUDGET_ OVERRUN

Nullabl e

FLOAT

Stores the project budget overrun information

No

ISSUE_HEALTH_ INDICATOR

Nullabl e

VARCHA R(255)

Stores the project health information

No

PROJECT_NAME

Nullabl e

VARCHA R(300)

Stores the name of the project record

No

PROJECT_ DESCRIPTION

Nullabl e

VARCHA R(650)

Stores the description of the project record

No

ASSOCIATED_ MSP_PROJECT

Nullabl e

VARCHA R(255)

Stores the file name and path of the microsoft project if the project is integrated with any MSP file

No

PROJECT_ START_DATE

Nullabl e

DATE

Provides start period of the project

No

PROJECT_END_ DATE

Nullabl e

DATE

Provides end period of the project

No

PROJECT_TYPE_ ID

Nullabl e

INT

Provides project type information for the project

Yes

PPROJECT_ TYPE_NAME

Nullabl e

VARCHA R(255)

Provides project type name for the project

Yes

PEOJECT_TYPE_ DESCRIPTION

Nullabl e

VARCHA R(650)

Provides project type description for the project

No

PROJECT_ REQUEST_ TYPE_ID

Nullabl e

INT

Stores the request information associated with each project

entity. It is a foreign key reference to REQUEST_ID of DIM_REQUESTS table.

Yes

PROJECT_ REQUEST_NAME

Nullabl e

VARCHA R(255)

Stores the request name associated with each project entity

Yes

ISSUE_ REQUEST_ TYPE_ID

Nullabl e

INT

Provides issue request type information for the project

Yes

ISSUE_ REQUEST_ TYPE_NAME

Nullabl e

VARCHA R(255)

Provides issue request type name for the project

Yes

RISK_REQUEST_ TYPE_ID

Nullabl e

INT

Provides risk request type information for the project

Yes

RISK_REQUEST_ TYPE_NAME

Nullabl e

VARCHA R(255)

Provides risk request type name for the project

Yes

SCOPE_ CHANGE_ TYPE_ ID

Nullabl e

INT

Provides scope change request type information for the project

Yes

SCOPE_ CHANGE_TYPE_ NAME

Nullabl e

VARCHA R(255)

Provides scope change request type name for the project

Yes

PROJECT_PT_ SETTINGS_KEY

Nullabl e

INT

Stores the foreign key to ITG_ SETTINGS_CONTAINERS table

No

PROJECT_ CONTEXT_PATH

Nullabl e

VARCHA R(255)

Stores unique name of the containers

No

PROJECT_ WORK_LOAD_ CATEGORY_ CODE

Nullabl e

VARCHA R(255)

Represents the workload category. This is the same value from the project settings but allocated a dedicated column for performance reasons.

Yes

PFM_REQUEST_ ID

Nullabl e

INT

Unique identifier for request

Yes

PFM_REQUEST_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_REQUEST

 

Yes

PFM_REQUEST_ ENTERPRISE_ KEY

Nullabl e

INT

Unique identifier for foreign key

 

Yes

REQUEST_ DESCRIPTION

Nullabl e

VARCHA R(400)

Short description of the selected request

No

REQUEST_ RELEASE_DATE

Nullabl e

DATE

Date wherein this request was released

No

REQUEST_ STATUS_ID

Nullabl e

INT

Identifier for the current request status

No

REQUEST_ DEPARTMENT_ CODE

Nullabl e

VARCHA R(30)

Lookup code for DEPARTMENT

Yes

REQUEST_ DEPARTMENT_ NAME

Nullabl e

VARCHA R(150)

Department name of the request

Yes

REQUEST_ PRIORITY_CODE

Nullabl e

VARCHA R(30)

Lookup code for PRIORITY

No

REQUEST_ PRIORITY_NAME

Nullabl e

VARCHA R(150)

priority name of the request

No

REQUEST_ APPLICATION

Nullabl e

VARCHA R(30)

Lookup code for APPLICATION

No

REQUEST_ APPLICATION_ NAME

Nullabl e

VARCHA R(150)

Application name of the request

No

REQUEST_ PROJECT_CODE

Nullabl e

VARCHA R(30)

Lookup code for REQUEST_ GROUP

No

REQUEST_ PROJECT_NAME

Nullabl e

VARCHA R(150)

Project name of the request

No

REQUEST_ CONTACT_ID

Nullabl e

INT

Identifier for the contacts associated with this record

No

REQUEST_ CONTACT_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_CONTACTS

 

No

REQUEST_ CONTACT_ ENTERPRISE_ KEY

Nullabl e

INT

Unique identifier for foreign key

 

No

REQUEST_ CONTACT_NAME

Nullabl e

VARCHA R(200)

Contact name related with the request

No

REQUEST_ CONTACT_ EMAIL_ ADDRESS

Nullabl e

VARCHA R(80)

Contact’s email address related with the request

No

REQUEST_ CONTACT_ PHONE_ NUMBER

Nullabl e

VARCHA R(30)

Contact’s phone number related with the request

No

REQUEST_ COMPANY

Nullabl e

VARCHA R(30)

Contact’s company related with the request

No

REQUEST_ STATUS_CODE

Nullabl e

VARCHA R(30)

Lookup code for WF_PARENT_STATUS

No

REQUEST_ STATUS_NAME

Nullabl e

VARCHA R(150)

Status name of the request

No

REQUEST_ PERCENT_ COMPLETE

Nullabl e

NUMERI C

Percentage of completion of this request (100 if the request is closed)

No

PROJECT_ DISPLAY_ STATUS

Nullabl e

VARCHA R(255)

This is the status end user sees from the application, which can be different from the internal status that is stored in the STATUS column.

No

PROJECT _ STATUS

Nullabl e

INT

Provides the project internal status, which must not be visible to the end user and is used internally to keep in sync with the associated request's internal status.

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

PROJECTS_ENTERPRISE_XREF_SEQ

MD_ENTERPRISE_KEY

PROJECTS_KEY_LOOKUP_SEQ

PK_ PROJECTS_ID

RESOURCE_POOLS_ENTERPRISE_XREF_SEQ

RESOURCE_POOL_ENTERPRISE_KEY

RESOURCE_POOLS_KEY_LOOKUP_SEQ

RESOURCE_POOL_ID

REQUESTS_ENTERPRISE_XREF_SEQ

REQUEST_ENTERPRISE_KEY

REQUESTS_KEY_LOOKUP_SEQ

REQUEST_ID

CONTACTS_ENTERPRISE_XREF_SEQ

CONTACT_ENTERPRISE_KEY

CONTACTS_KEY_LOOKUP_SEQ

CONTACT_ID