DW_EB_X_EBS_COMMON_W_POSITION_D
-DW_EB_X_EBS_COMMON_W_POSITION_D stores the parent-child relationship for Manager-Employee and Resource-Resource Manager. This table also stores information about the Assignments of Employee, Position_code, Division\Organization of Resource etc.-The grain of this table is -PERSON_ID associated with HR Employee for Manager Hierarchy,considering only PRIMARY ASSIGNEMENT. -RESOURCE_ID (As mentioned , the hierarchy is not supported)-This table is designed to be a slowly changing dimension that supports Type-2 changes.-This table brings history from Source Database.
Details
Module: Common Tables (EBS_COMMON)
Key Columns
Key column information is not documented in the supplied metadata.
Columns
| Name | Datatype | Length | Precision | Not Null | Comments | Referred Table |
|---|---|---|---|---|---|---|
| ROW_WID | NUMBER | 38 | True | Surrogate key to uniquely identify a record. | ||
| ACTIVE_FLG | VARCHAR2 | 1 CHAR | It says whether Relation is active or inactive | |||
| ASSIGNMENT_ID | NUMBER | 18 | Assignment Id of Assignment associated to HR Employee. Null for Sales Resource. | |||
| AUX1_CHANGED_ON_DT | TIMESTAMP | 6 | Oracle System Field. This column identifies the last modified date and time of the auxiliary table's record. | |||
| AUX2_CHANGED_ON_DT | TIMESTAMP | 6 | Oracle System Field. This column identifies the last modified date and time of the auxiliary table's record. | |||
| AUX3_CHANGED_ON_DT | TIMESTAMP | 6 | Oracle System Field. This column identifies the last modified date and time of the auxiliary table's record. | |||
| AUX4_CHANGED_ON_DT | TIMESTAMP | 6 | Oracle System Field. This column identifies the last modified date and time of the auxiliary table's record. | |||
| CHANGED_BY_WID | NUMBER | 38 | This is a foreign key to the DW_EB_X_EBS_COMMON_W_USER_D dimension indicating the user who last modified the record in the source system. | |||
| CHANGED_ON_DT | TIMESTAMP | 6 | Identifies the date and time when the record was last modified in the source system. | |||
| CREATED_BY_WID | NUMBER | 38 | This is a foreign key to the DW_EB_X_EBS_COMMON_W_USER_D dimension indicating the user who created the record in the source system. | |||
| CREATED_ON_DT | TIMESTAMP | 6 | Identifies the date and time when the record was initially created in the source system. | |||
| CURRENT_FLG | VARCHAR2 | 1 CHAR | This is a flag for marking dimension records as "Y" in order to represent the current state of a dimension entity. This flag is typically critical for Type II slowly changing dimensions, as records in a Type II situation tend to be numerous | |||
| DATASOURCE_NUM_ID | NUMBER | 10 | Unique identifier of the source system from which data was extracted. In order to be able to trace the data back to its source, it is recommended that you define separate unique source IDs for each of your different source instances. | |||
| DELETE_FLG | VARCHAR2 | 1 CHAR | This flag indicates the deletion status of the record in the source system. A value of Y indicates the record is deleted from the source system and logically deleted from the data warehouse. A value of N indicates that the record is active. | |||
| DIVN_CODE | VARCHAR2 | 30 CHAR | Code of the Primary Division the position belongs to. | |||
| EFFECTIVE_FROM_DT | TIMESTAMP | 6 | This column stores the date from which the dimension record is effective. A value is either assigned by Oracle BI Applications or extracted from the source. | |||
| EFFECTIVE_TO_DT | TIMESTAMP | 6 | This column stores the date up to which the dimension record is effective. A value is either assigned by Oracle BI Applications or extracted from the source. | |||
| EMP_FST_NAME | VARCHAR2 | 150 CHAR | First Name of the primary employee who holds this position | |||
| EMP_ID | VARCHAR2 | 30 CHAR | Key of the Primary Employee holding this position in the Source system | |||
| EMP_LAST_NAME | VARCHAR2 | 150 CHAR | Last Name of the primary employee who holds this position | |||
| EMP_LOGIN | VARCHAR2 | 100 CHAR | Login of the primary employee who holds this position | |||
| ETL_PROC_WID | NUMBER | 38 | Oracle System Field. This column is the unique identifier for the specific ETL process used to create or update this data. | |||
| INTEGRATION_ID | VARCHAR2 | 80 CHAR | Unique identifier of a dimension or fact entity in its source system. In case of composite keys, the value in this column can consist of concatenated parts. | |||
| JOB_ROLE_CODE | VARCHAR2 | 30 CHAR | Code of the Job or Role of the resource/employee to whom the position belongs to. | |||
| OWNER_ALLOC | NUMBER | 28,10 | Attributed sales ownership ratio allocated to this position. Ratio range from 0, representing no sales attributed to the position to 1, representing 100% of sales attributed to the position. | |||
| PARENT_TYPE | VARCHAR2 | 30 CHAR | Parent Type of the Employee holding the position ex Line Manager | |||
| PAR_INTEGRATION_ID | VARCHAR2 | 80 CHAR | Parent Position INTEGARTION_ID. | |||
| POSITION_CODE | VARCHAR2 | 30 CHAR | Code of the position. | |||
| POSTN_TYPE_CODE | VARCHAR2 | 240 CHAR | User defined position type code from Source system. User can assign special type to each position, such as 'Mirror', 'Shared', etc. to denote particular attribute of the position. | |||
| PRIMARY_FLG | VARCHAR2 | 1 CHAR | Y, If the Assignment_Id is for the Primary Assignment of the HR EmployeeThis column is added to accommodate Info. of Manager Hierarchy. | |||
| SCD1_WID | NUMBER | 38 | Surrogate key to identify records for given INTEGRATION_ID. | |||
| SRC_EFF_FROM_DT | TIMESTAMP | 6 | Stores the date from which the source record (in the source system) is effective. The value is extracted from the source whenever available. | |||
| SRC_EFF_TO_DT | TIMESTAMP | 6 | Stores the date to which the source record (in the source system) is effective. The value is extracted from the source whenever available. | |||
| 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. | |||
| TYPE_FLG | VARCHAR2 | 1 CHAR | This flag indicates the operational type of this position. This is a custom flag defined in placeholder column to distinguish different operational position hierarchy. A value of "P" indicates that the position belong to "Primary" type where position is used in operational Source system. A value of "A" indicates that the position belongs to "Alternate" type where position is used in external compensational source. | |||
| VIS_BU_ID | VARCHAR2 | 15 CHAR | Identifies the Integration identifier of the Employee's Primary business unit. This is used to handle visibility | |||
| VIS_POS_ID | VARCHAR2 | 15 CHAR | Identifies the Integration identifier of the Employee's Primary Position. This is used to handle visibility. | |||
| VIS_PR_EMP_ID | VARCHAR2 | 15 CHAR | Identifies the Integration identifier of the Employee's Primary ID. This is used to handle visibility. | |||
| X_CUSTOM | VARCHAR2 | 10 CHAR | Column used as a generic field for customer extensions. | |||
| W$_INSERT_DT | TIMESTAMP | 6 | ||||
| W$_UPDATE_DT | TIMESTAMP | 6 |