FACT_RESOURCE_ALLOCATION

This table stores the daily allocated and actual effort (in hours) for staffing profile resource assignments.

Foreign Keys

Primary Key Table

Primary Key Column

Foreign Key Column

DIM_RESOURCE_POOLS

ASSIGNMENT_RESOURCE_POOL_ID

PK_ RESOURCE_POOLS_ID

DIM_RESOURCES

RESOURCE_ID

PK_RESOURCES_ID

DIM_ASSETS

ASSET_ID

PK_ASSETS_ID

DIM_PROPOSALS

PROPOSAL_ID

PK_PROPOSALS_ID

DIM_PROJECTS

PROJECT_ID

PK_PROJECTS_ID

DIM_POSITIONS

POSITION_ID

PK_POSITIONS_ID

Column Descriptions

Column Name

Null?

Data Type

Description

PK_RESOURCE_ALLOCATION_ID

Not Null

INT

System-generated identifier

MD_BUSINESS_KEY

Nullable

VARCHAR(1000)

Primary key that is mapped to one or more source columns

 

MD_ENTERPRISE_KEY

Nullable

INT

Unique identifier for entity

RESOURCE_ASSIGNMENT_ID

Nullable

INT

Indicates the staffing profile resource assignment

ASSIGNMENT_STATUS_CODE

Nullable

INT

The code that represents the status of the resource assignments. The code is decoded by the application to represent the meaning on the user interface. The status of the assignment can be soft-booked or committed.

1:Soft Booked 2: Committed

ASSIGNMENT_STATUS_NAME

Nullable

VARCHAR(15)

DECODE(STATUS_CODE; 1: Soft Booked; 2: Committed; UNKNOWN)

ASSIGNMENT_RESPONSE_DATE

Nullable

DATE

Indicates the date when the first response was given to the position's request. It can be same as created date, but created date is mostly used for auditing purposes.

ASSIGNMENT_DEFAULT_VIEW_EFFORT_TYPE

Nullable

INT

This is the effort type (FTE, month, person days) that the user wants to see the data in.

0: FTE; 1:HOURS; 2:PERSON_DAYS

ASSIGNMENT_DEFAULT_VIEW_EFFORT_TYPE_NAME

Nullable

VARCHAR(20)

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

ASSIGNMENT_RESOURCE_POOL_ID

Nullable

INT

Unique identifier for assignment resource pool

ASSIGNMENT_RESOURCE_POOL_BUSINESS_KEY

Nullable

VARCHAR(1000)

Foreign key that is mapped to DIM_RESOURCE_POOLS

 

ASSIGNMENT_RESOURCE_POOL_ENTERPRISE_KEY

Nullable

INT

Unique identifier for foreign key

 

ASSIGNMENT_DEF_VIEW_PERIOD_TYPE

Nullable

INT

8: WEEK;

4: MONTH;

5: QUARTER;

7: HALF_YEAR;

6: YEAR

ASSIGNMENT_DEF_VIEW_PERIOD_TYPE_NAME

Nullable

VARCHAR(10)

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

POSITION_ID

Nullable

INT

Unique identifier for position

POSITION_BUSINESS_KEY

Nullable

VARCHAR(1000)

Foreign key that is mapped to DIM_POSITIONS

 

POSITION_ENTERPRISE_KEY

Nullable

INT

Unique identifier for foreign key

 

RESOURCE_ID

Nullable

INT

Unique identifier for resource

RESOURCE_BUSINESS_KEY

Nullable

VARCHAR(1000)

Foreign key that is mapped to DIM_ RESOURCES

 

RESOURCE_ENTERPRISE_KEY

Nullable

INT

Unique identifier for foreign key

 

STAFFING_PROFILE_ID

Nullable

INT

Indicates the staffing profile

ALLOCATION_DATE

Nullable

DATE

Indicates the date for the allocation

ALLOCATION_VALUE

Nullable

NUMERIC

Indicates the actual effort in hours

CONTINER_ENTITY_TYPE_CODE

Nullable

INT

Indicates what this staffing profile is used for - strategic projects, legacy systems, and so on

CONTINER_ENTITY_ID

Nullable

INT

Foreign key to one of the container entity tables

PROJECT_ID

Nullable

INT

Unique identifier for project

PROJECT_BUSINESS_KEY

Nullable

VARCHAR(1000)

Foreign key that is mapped to DIM_ PROJECTS

 

PROJECT_ENTERPRISE_KEY

Nullable

INT

Unique identifier for foreign key

 

PROPOSAL_ID

Nullable

INT

Unique identifier for proposal

PROPOSAL_BUSINESS_KEY

Nullable

VARCHAR(1000)

Foreign key that is mapped to DIM_ PROPOSALS

 

PROPOSAL_ENTERPRISE_KEY

Nullable

INT

Unique identifier for foreign key

 

ASSET_ID

Nullable

INT

Unique identifier for asset

ASSET_BUSINESS_KEY

Nullable

VARCHAR(1000)

Foreign key that is mapped to DIM_ ASSETS

 

ASSET_ENTERPRISE_KEY

Nullable

INT

Unique identifier for foreign key

 

MD_BATCH_ID

Nullable

INT

ID of data batch

MD_PROCESS_ID

Nullable

INT

ID of ETL process running on a specific task

MD_SOURCE_INSTANCE_ID

Nullable

INT

Content pack ID of source instance

MD_CREATEDDATE

Nullable

DATETIME

Date of the record being inserted

MD_DELETEDDATE

Nullable

DATETIME

Date of the record being deleted

MD_LASTMODDDATE

Nullable

DATETIME

Date of the record being updated

MD_ACTIVESTATUSIND

Nullable

VARCHAR(1)

 Flags whether or not the old record is deleted

MD_HASH_CODE

Nullable

VARCHAR(40)

 Hash code that is generated

Projections

This table uses no projections.

Sequences

Sequence Name

Sequence Type

RESOURCE_ALLOCATION_ENTERPRISE_XREF_SEQ

MD_ENTERPRISE_KEY

RESOURCE_ALLOCATION_KEY_LOOKUP_SEQ

PK_ RESOURCE_ALLOCATION_ID

ASSETS_ENTERPRISE_XREF_SEQ

ASSET_ENTERPRISE_KEY

ASSETS_KEY_LOOKUP_SEQ

ASSET_ID

PROPOSALS_ENTERPRISE_XREF_SEQ

PROPOSAL_ENTERPRISE_KEY

PROPOSALS_KEY_LOOKUP_SEQ

PROPOSAL_ID

PROJECTS_ENTERPRISE_XREF_SEQ

PROJECT_ENTERPRISE_KEY

PROJECTS_KEY_LOOKUP_SEQ

PROJECT_ID

POSITIONS_ENTERPRISE_XREF_SEQ

POSITION_ENTERPRISE_KEY

POSITIONS_KEY_LOOKUP_SEQ

POSITION_ID

RESOURCES_ENTERPRISE_XREF_SEQ

RESOURCE_ENTERPRISE_KEY

RESOURCES_KEY_LOOKUP_SEQ

RESOURCE_ID

RESOURCE_POOLS_ENTERPRISE_XREF_SEQ

RESOURCE_POOL_ENTERPRISE_KEY

RESOURCE_POOLS_KEY_LOOKUP_SEQ

RESOURCE_POOL_ID