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