DW_EB_X_EBS_COMMON_W_POSITION_DH

DW_EB_X_EBS_COMMON_W_POSITION_DH stores maximum 18 level hierarchy for the HR PERSON and RESOURCE, for each level it also stores DIVI\ORGANIZATION_CODE , POSITION_CODE etc.DW_EB_X_EBS_COMMON_W_POSITION_D has 18 FK's to this table for each level.This table is designed to be a slowly changing dimension that supports Type-2 changesThe Employee- Manager relationship for the hierarchy is stored in column-flattened modelThis level in the table are padded by the leaf node at lower level.Eg: For 18 level hierachy if only 3 nodes are present (Top, Lvl 16, Lvl15) then rest level from Lvl 14 to lvel1 and BASE level are padded with lvl 15 info.

Details

Module: Common Tables (EBS_COMMON)

Key Columns

Key column information is not documented in the supplied metadata.

Columns

Columns
Name	Datatype	Length	Precision	Not Null	Comments	Referred Table
ROW_WID	NUMBER		38	True	Surrogate key to uniquely identify a record.
BASE_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the base position.
BASE_DIVN_CODE	VARCHAR2	240 CHAR			"Code of the Primary Division for base level
BASE_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the Base level Position.
BASE_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the base level position in the Source system.
BASE_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of Job/Role. for base level
BASE_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding base position
BASE_POSTN_CODE	VARCHAR2	50 CHAR			Code of the base Position.
CURRENT_FLG	VARCHAR2	1 CHAR			Indicates whether this hierarchy record is current or not. At anytime, there should be only one record for each POSITION_WID, for which this flag is set to 'Y'
DATASOURCE_NUM_ID	NUMBER		10		This column is the unique identifier of the source system from which data was extracted. In order to be able to trace the data back to its source, Oracle recommends that you define separate unique source IDs for each of your different source instances.
DELETE_FLG	VARCHAR2	1 CHAR			This flag is used for physical deletion of record during record processing.
EFFECTIVE_FROM_DT	TIMESTAMP		6		DATE/TIMESTAMP this particular record (or hierarchy structure) starting to be effective.
EFFECTIVE_TO_DT	TIMESTAMP		6		DATE/TIMESTAMP this record (or hierarchy structure) ceased to be effective.
ETL_PROC_WID	NUMBER		38		System Field. This column is the unique identifier for the specific ETL process used to create or upDATE/TIMESTAMP.
FIXED_HIER_LEVEL	NUMBER		10		Level of the last Position, node, in the hierarchy
LVL10ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 10 position.
LVL10ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 10 Position belongs to.
LVL10ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 10 position in the Source system
LVL10ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 10 Job/Role
LVL10ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 10 Position.
LVL10ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 10 Position
LVL10_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 10 Position
LVL11ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 11 position.
LVL11ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 11 Position belongs to.
LVL11ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 11 position in the Source system
LVL11ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 11 Job/Role
LVL11ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 11 Position.
LVL11ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 11 Position
LVL11_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 11 Position
LVL12ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 12 position.
LVL12ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 12 Position belongs to.
LVL12ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 12 position in the Source system
LVL12ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 12 Job/Role
LVL12ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 12 Position.
LVL12ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 12 Position
LVL12_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 12 Position
LVL13ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 13 position.
LVL13ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 13 Position belongs to.
LVL13ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 13 position in the Source system
LVL13ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 13 Job/Role
LVL13ANC_LOGIN	VARCHAR2	100 CHAR
LVL13ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 13 Position.
LVL13_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 13 Position
LVL14ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 14 position.
LVL14ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 14 Position belongs to.
LVL14ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 14 position in the Source system
LVL14ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 14 Job/Role
LVL14ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 14 Position.
LVL14ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 14 Position
LVL14_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 14 Position
LVL15ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 15 position.
LVL15ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 15 Position belongs to.
LVL15ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 15 position in the Source system
LVL15ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 15 Job/Role
LVL15ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 15 Position.
LVL15ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 15 Position
LVL15_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 15 Position
LVL16ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 16 position.
LVL16ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 16 Position belongs to.
LVL16ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 16 position in the Source system
LVL16ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 16 Job/Role
LVL16ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 16 Position.
LVL16ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 16 Position
LVL16_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 16 Position
LVL1ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 1 position.
LVL1ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division for level 1 employee
LVL1ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding
LVL1ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 1 Job/Role
LVL1ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding level 1.
LVL1ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 1 Position.
LVL1_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 1 Position.
LVL2ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 2 position.
LVL2ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 2 Position belongs to.
LVL2ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 2 position in the Source system
LVL2ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 2 Job/Role
LVL2ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 2 Position.
LVL2ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 2 Position
LVL2_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 2 Position
LVL3ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 3 position.
LVL3ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 3 Position belongs to.
LVL3ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 3 position in the Source system
LVL3ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 3 Job/Role
LVL3ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 3 Position.
LVL3ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 3 Position
LVL3_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 3 Position
LVL4ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 4 position.
LVL4ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 4 Position belongs to.
LVL4ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 4 position in the Source system
LVL4ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 4 Job/Role
LVL4ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 4 Position.
LVL4ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 4 Position
LVL4_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 4 Position
LVL5ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 5 position.
LVL5ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 5 Position belongs to.
LVL5ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 5 position in the Source system
LVL5ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 5 Job/Role
LVL5ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 5 Position.
LVL5ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 5 Position
LVL5_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 5 Position
LVL6ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 6 position.
LVL6ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 6 Position belongs to.
LVL6ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 6 position in the Source system
LVL6ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 6 Job/Role
LVL6ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 6 Position.
LVL6ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 6 Position
LVL6_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 6 Position
LVL7ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 7 position.
LVL7ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 7 Position belongs to.
LVL7ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 7 position in the Source system
LVL7ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 7 Job/Role
LVL7ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 7 Position.
LVL7ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 7 Position
LVL7_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 7 Position
LVL8ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 8 position.
LVL8ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 8 Position belongs to.
LVL8ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 8 position in the Source system
LVL8ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 8 Job/Role
LVL8ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 8 Position.
LVL8ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 8 Position
LVL8_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 8 Position
LVL9ANC_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level 9 position.
LVL9ANC_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level 9 Position belongs to.
LVL9ANC_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level 9 position in the Source system
LVL9ANC_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level 9 Job/Role
LVL9ANC_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level 9 Position.
LVL9ANC_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level 9 Position
LVL9_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level 9 Position
PARENT_TYPE	VARCHAR2	30 CHAR			Holds the manager type of the employee.
POSITION_WID	NUMBER		38		Foreign key to the Base dimension DW_EB_X_EBS_COMMON_W_POSITION_D	DW_EB_X_EBS_COMMON_W_POSITION_D
PRIMARY_FLG	VARCHAR2	1 CHAR			Indicates whether the assignment is primary or not. At anytime, the assignment is primary this flag is set to 'Y'.
SCD1_WID	NUMBER		38		Surrogate key to identify records with same INTEGRATION_ID.
TENANT_ID	VARCHAR2	80 CHAR			Unique identifier for a tenant in a multi-tenant environment. This column is typically be used in an Application Service Provider (ASP)/Software As a Service (SOAS) model. Column used as a generic field for customer extensions.
TOP_EMP_FULL_NAME	VARCHAR2	302 CHAR			Full Name of the Employee holding the level Top Position
TOP_LVL_ASSIGNMENT_ID	NUMBER		18		Assignment key of the Employee at the level Top position.
TOP_LVL_DIVN_CODE	VARCHAR2	240 CHAR			Code of the Primary Division the Level Top Position belongs to.
TOP_LVL_EMP_ID	VARCHAR2	30 CHAR			Key of the Employee holding the level Top position in the Source system
TOP_LVL_JOB_ROLE_CODE	VARCHAR2	30 CHAR			Code of the Level Top Job/Role
TOP_LVL_LOGIN	VARCHAR2	100 CHAR			Login of the Employee holding the Level Top Position.
TOP_LVL_POSTN_CODE	VARCHAR2	50 CHAR			Code of the Level Top Position
X_CUSTOM	VARCHAR2	10 CHAR			Column used as a generic field for customer extensions.
W$_INSERT_DT	TIMESTAMP		6
W$_UPDATE_DT	TIMESTAMP		6