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

Nullabl e

VARCHA R(1000)

Primary key that is mapped to one or more source columns

 

MD_ENTERPRISE_ KEY

Nullabl e

INT

Unique identifier for entity

RESOURCE_ ASSIGNMENT_ID

Nullabl e

INT

Indicates the staffing profile resource assignment

ASSIGNMENT_ STATUS_CODE

Nullabl e

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

Nullabl e

VARCHA R(15)

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

ASSIGNMENT_ RESPONSE_DATE

Nullabl e

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

Nullabl e

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

Nullabl e

VARCHA R(20)

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

ASSIGNMENT_ RESOURCE_ POOL_ID

Nullabl e

INT

Unique identifier for assignment resource pool

ASSIGNMENT_ RESOURCE_ POOL_BUSINESS_ KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_RESOURCE_POOLS

 

ASSIGNMENT_ RESOURCE_ POOL_ ENTERPRISE_KEY

Nullabl e

INT

Unique identifier for foreign key

 

ASSIGNMENT_ DEF_VIEW_ PERIOD_TYPE

Nullabl e

INT

8: WEEK;

4: MONTH;

5: QUARTER;

7: HALF_YEAR;

6: YEAR

ASSIGNMENT_ DEF_VIEW_ PERIOD_TYPE_ NAME

Nullabl e

VARCHA R(10)

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

POSITION_ID

Nullabl e

INT

Unique identifier for position

POSITION_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_POSITIONS

 

POSITION_ ENTERPRISE_KEY

Nullabl e

INT

Unique identifier for foreign key

 

RESOURCE_ID

Nullabl e

INT

Unique identifier for resource

RESOURCE_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_ RESOURCES

 

RESOURCE_ ENTERPRISE_KEY

Nullabl e

INT

Unique identifier for foreign key

 

STAFFING_ PROFILE_ID

Nullabl e

INT

Indicates the staffing profile

ALLOCATION_ DATE

Nullabl e

DATE

Indicates the date for the allocation

ALLOCATION_ VALUE

Nullabl e

NUMERIC

Indicates the actual effort in hours

CONTINER_ ENTITY_TYPE_ CODE

Nullabl e

INT

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

CONTINER_ ENTITY_ID

Nullabl e

INT

Foreign key to one of the container entity tables

PROJECT_ID

Nullabl e

INT

Unique identifier for project

PROJECT_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_ PROJECTS

 

PROJECT_ ENTERPRISE_KEY

Nullabl e

INT

Unique identifier for foreign key

 

PROPOSAL_ID

Nullabl e

INT

Unique identifier for proposal

PROPOSAL_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_ PROPOSALS

 

PROPOSAL_ ENTERPRISE_KEY

Nullabl e

INT

Unique identifier for foreign key

 

ASSET_ID

Nullabl e

INT

Unique identifier for asset

ASSET_ BUSINESS_KEY

Nullabl e

VARCHA R(1000)

Foreign key that is mapped to DIM_ ASSETS

 

ASSET_ ENTERPRISE_KEY

Nullabl e

INT

Unique identifier for foreign key

 

MD_BATCH_ID

Nullabl e

INT

ID of data batch

MD_PROCESS_ID

Nullabl e

INT

ID of ETL process running on a specific task

MD_SOURCE_ INSTANCE_ID

Nullabl e

INT

Contnet pack ID of source instance

MD_ CREATEDDATE

Nullabl e

DATETIM E

Date of the record being inserted

MD_ DELETEDDATE

Nullabl e

DATETIM E

Date of the record being deleted

MD_ LASTMODDDATE

Nullabl e

DATETIM E

Date of the record being updated

MD_ ACTIVESTATUSIN D

Nullabl e

VARCHA R(1)

Flags whether or not the old record is deleted

MD_HASH_CODE

Nullabl e

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