View source | View content page | Page history | Printable version   

Projects:Humancapitalmanagement/Employee Information Management/Technical Documentation

Contents

Overview

This article will explain how to proceed in order to create the Employee Information Management module within the Human Capital Management project.

ER Diagram

The following diagram represents the semantic data model of the system that will be later mapped to a physical model.

HCM ERDiagram.jpg

This diagram should be read like:

  • An employee is a business partner.
  • Each employee has got at least one contact.
  • Each contact belongs to one and only one employee.
  • An employee has got at least one location.
  • Each location belongs to one a only one employee.
  • An employee has got at least one bank account.
  • Each bank account belongs to one a only one employee.
  • An employee can have relatives.
  • Each relative belongs to one a only one employee.
  • A Manager is an employee.
  • Each team has got one and only one manager.
  • Each manager can manage one or more teams.
  • An employee works in one and only one department.
  • An department has at least one employee working on it.
  • An employee is part of one and only one organization.
  • An organization can have zero or many employees working on it.
  • A salary category is linked to one an only one organization.
  • An organization is linked to many salary categories.
  • A position is linked to one an only one organization.
  • An organization is linked to many positions.
  • An employee has at least a job and a salary category assigned for certain time period with a certain salary.
  • A salary category can be mapped with more than one position and more than one organization.
  • A position can be mapped with more than one salary category and more than one organization.
  • An organization can be mapped with more than one position and more than one salary category.

Relational Model

The previous model is mapped as shown in the following model. NOTE: Not all the attributes of each table are shown in this diagram.

HCM RelationalModel.jpg

Constraints and technical decisions

During the design of the module some issues have being found that should be taken into consideration. There are some issues concerning employees being business partners. Some other issues are related to previous functionality already provided by Openbravo ERP. And there are finally some constraints due to Openbravo Platform.

Employees are Business Partners

In Openbravo ERP and in the model for this module, the employees inherits from Business Partners. That is, employees are business partners with some extra attributes. There are some issues regarding this fact that needs to be dealt with.

Functionality already present in Openbravo ERP

Openbravo Platform

List of modified items

This module will only depend on core.

The following tables will be created for this module:

The following core tables will have some fields added:

The following table will be used in windows/tabs of the module - but no columns will be added:

Physical data model description

As it can be seen in the previous images, employees will be mapped as business partners. Some new columns will have to be added to this table in order to keep the new data that is required to be maintained within the system. In the following tables, it can be seen which columns of business partners are being used and which columns will be added.

New tables

Defaults Table

HCMC_Default
Column Name Type Length Comments
HCMC_DEFAULT_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_BP_CATEGORY CHAR 32 Foreign key to C_BP_CATEGORY indicating the employee category used in the creation of employees.
PAYMENTRULE CHAR 32 Form of payment Default value used in creation of employees for expense Reimbursement Information
M_PRICELIST_ID CHAR 32 Purchase Pricelist Default value used in creation of employees for expense Reimbursement Information
C_PAYMENTTERM_ID CHAR 32 Payment Terms Default value used in creation of employees for expense Reimbursement Information

Team Table

HCMC_Team
Column Name Type Length Comments
HCMC_TEAM_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
NAME CHAR 32 Name of the team
CURRENT_MANAGER CHAR 32 Foreign key to C_BPARTNER indicating which employee is current manager of the team.
ADDMANAGER CHAR 1 Button linked to a PL which inserts data in HCMC_TEAM_MANAGER table
ISSUMMARY CHAR 1 Necessary to create a tree structure.

Manager Table

HCMC_Team_Manager
Column Name Type Length Comments
HCMC_TEAM_MANAGER_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
HCMC_TEAM_ID CHAR 32 Foreign key to HCMC_TEAM indicating which employee is manager of the record.
MANAGER CHAR 32 Foreign key to C_BPARTNER indicating which employee is manager of the record.
EFFECTIVEFROM DATE Day the record is valid from
EFFECTIVETO DATE Day the record is valid to

Position Table

HCMC_Position
Column Name Type Length Comments
HCMC_POSITION_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
NAME CHAR 32

Contract Type Table

HCMC_Contracttype
Column Name Type Length Comments
HCMC_CONTRACTTYPE_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
NAME CHAR 32
VALUE CHAR 32

Position + Salary Category Table

HCMC_Position_SalCat
Column Name Type Length Comments
HCMC_POSITION_SALCAT_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
HCMC_POSITION_ID CHAR 32 Positions ID
HCMC_SALARY_CATEGORY_ID CHAR 32 Salary Categories ID


Employment information Table

HCMC_employmentinformation
Column Name Type Length Comments
HCMC_EMPLOYMENTINFORMATION_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_BPARTNER_ID CHAR 32 Foreign key to C_BPARTNER. Employee this employment information belongs to.
HCMC_TEAM_ID CHAR 32 Foreign key to HCMC_TEAM. Team the employee is working at.
HCMC_POSITION_ID CHAR 32 Foreign key to HCMC_POSITION. Position the employee is working at.
HCMC_SALARY_CATEGORY_ID CHAR 32 Foreign key to HCMC_SALARY_CATEGORY. Salary Category of the employee.
HCMC_CONTRACTTYPE_ID CHAR 32 Foreign key to HCMC_CONTRACTTYPE. Contract type the employee is working with.
SALARY NUMBER 10 The actual amount of the salary.
FULL/PARTTIME CHAR 32 Dedication of the employee. Could be part time or full time.
STANDARDHOURS NUMBER 10 Hours worked for certain period
WORKPERIOD NUMBER 10 Period of time the standard hours referes to.
EFFECTIVETO DATE Date that this salary is valid from.
EFFECTIVEFROM DATE Date that this salary is valid from.
COMMENTS CHAR 255
COMPLETE CHAR 1
STATUS CHAR 32
BONUSPERCENTAGE NUMBER 10

Contact table

HCMC_contact
Column Name Type Length Comments
HCMC_CONTACT_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_BPARTNER_ID CHAR 32 Employee this contact belongs to.
CONTACTTYPE CHAR 32 Contact type
CONTACT CHAR 64 The contact indeed
ISDEFAULT CHAR 1 Y/N column. Y = it is default contact; M = It's not default.
MAKEDEFAULT CHAR 1 Button to call a PL that will check the record to default.
ISBUSINESS CHAR 1 Y/N column. Y = It's a business contact; M = It is not a business contact.
COMMENTS CHAR 255

Family Member Table

HCMC_familymember
Column Name Type Length Comments
HCMC_FAMILYMEMBER_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_BPARTNER_ID CHAR 32 Employee this family member belongs to.
FIRSTNAME CHAR 32
SECONDNAME CHAR 32
MIDDLENAME CHAR 32
KNOWNAS CHAR 64
RELATION CHAR 32 Relation between the family member and the employee.
GENDER CHAR 32
EMAIL CHAR 64
PHONE NUMBER 20
MOBILE PHONE NUMBER 20
IDDOCUMENT CHAR 32 Identification Document: ID or Passport
TAXID CHAR 32
BIRTHDATE DATE
BIRTHPLACE CHAR 32
C_COUNTRY_ID CHAR 32 Country of Citizenship
COMMENTS CHAR 255

Other citizenship Table

HCMC_OTHERCITIZENSHIP
Column Name Type Length Comments
HCMC_OTHERCITIZENSHIP_ID CHAR 32 The primary key of the table that must follow the table name followed by the _ID.
AD_CLIENT_ID CHAR 32 Indicates which client (company) the record belongs to.
AD_ORG_ID CHAR 32 Indicates which organization (city/department/location) within a client a record belongs to.
ISACTIVE CHAR 1 Intended for deactivating records that are not valid anymore but are referenced within the system and hence cannot be deleted.
CREATED DATE Date/time of creation of a record.
CREATEDBY CHAR 32 Foreign key to AD_USER indicating the user that created this record.
UPDATED DATE Date/time of last update of a record.
UPDATEDBY CHAR 32 Foreign key to AD_USER indicating the user that last updated this record.
C_BPARTNER_ID CHAR 32 Employee this family member belongs to.
C_COUNTRY_ID CHAR 32 Country of Citizenship

Columns added to Core tables

In c_bpartner table, two custom columns will be added. This columns will be available at the application just in case some extra fields are required. This way, it is not necessary to get to database level to customize a pair of fields of the module.

Columns added to C_BPARTNER table

C_bpartner
Column Name Type Length Comments
EM_HCMC_empnumber NUMBER 10 Employee number
EM_HCMC_IDdocument CHAR 32 ID
EM_HCMC_lastname CHAR 32
EM_HCMC_middlename CHAR 32
EM_HCMC_birthdate DATE
EM_HCMC_birthplace CHAR 32
EM_HCMC_cityzenship CHAR 32 Field Name = country of cityzenship
EM_HCMC_gender CHAR 32
EM_HCMC_maritalstatus CHAR 32
EM_HCMC_educationlevel CHAR 32
EM_HCMC_status CHAR 32
EM_HCMC_exitdate DATE
EM_HCMC_team_id CHAR 32
EM_HCMC_position_id CHAR 32
EM_HCMC_employementorg_id CHAR 32
EM_HCMC_education degree_id CHAR 32

Columns added to C_BP_BANKACCOUNT table

C_bp_bankaccount
Column Name Type Length Comments


EM_HCMC_Accountname CHAR 32
EM_HCMC_Accounttype CHAR 32
EM_HCMC_isDefault CHAR 1
EM_HCMC_makedefault CHAR 1 Button to make the record default
SWIFT CHAR 32 SWIFT or BIC identifies banks internationally

Columns added to C_BPARTNER_LOCATION table

C_bpartner_location
Column Name Type Length Comments


EM_HCMC_isDefault CHAR 1
EM_HCMC_makedefault CHAR 1 Button to make the record default
EM_HCMC_isBusiness CHAR 1
EM_HCMC_comments CHAR 255

Columns added to C_SALARY_CATEGORY table

C_Salary_Category
Column Name Type Length Comments
EM_HCMC_C_CURRENCY_ID CHAR 32 Foreign key to C_CURRENCY indicating the currency the salary is in.
EM_HCMC_FROMAMOUNT NUMBER 10 Minimum amount of salary for the salary category
EM_HCMC_TOAMOUNT NUMBER 10 Maximun amount of salary for the salary category
EM_HCMC_BONUSTYPE CHAR 60
EM_HCMC_FROMPERCENTAGE NUMBER 10
EM_HCMC_TOPERCENTAGE NUMBER 10

Comments regarding existing columns in Core tables

C_bpartner
Column Name Comments
value Field name in employee header will be: Search key
name Field name in employee header will be: first name
name2 Field name in employee header will be: known as
description Field name in employee header will be: comments
c_bp_group This value will be defined in Defaults window
isvendor This value will be set to Y if there are values in the defaults window.
iscustomer It will be set to 'N' on creation.
isemployee It will be set to 'y' on creation.
issalesrep This field will not be used in the employee window. It can be set from the business partner > employee window.
isworker This field will not be used in the employee window. It can be set from the business partner > employee window.
c_paymentterm_id This value will be defined in Defaults window
paymentrule This value will be defined in Defaults window
m_pricelist_id This value will be defined in Defaults window


C_bp_bankaccount
Column Name Comments
a_name, a_street, a_city, etc. Address columns will not be used
bank_name This column will be used
iban This column will be used
showaccountno, showiban This check boxes will be substituted with account type drop down, therefore, not used.


C_bpartner_location
Column Name Comments
phone, fax, etc. Contact columns will not be used
isbillto, isshipto, etc. These columns will not be used, they can still be set from the Business Partner window if necessary.


List of artifacts

In the following tables, the list of artifacts that needs to be implemented can be found. They have being categorized by artifact type.

Windows & Menu entries

The following windows, with their corresponding tabs and fields will be implemented. Also, a menu entry will be created for each of them.

Windows
Name Description Importance
Employee Used to manage employee information. The header is linked to c_bpartner table. Must have
Team Used to manage teams. The managers are managed form this window as well. Must have
Position Used to manage positions Must have
Salary Category Used to manage salary categories. Note: the window is already created. Only menu entry is needed. Must have
Contract type Used to manage contract types Must have
Manager View to see the information of the employees that are managers. Nice to have
Public Employee Information View that contains very basic information of every employee available for every employee. Nice to have
Employee Information Contains advanced private information of employees available only for Hr managers and team managers Nice to have

Message

The following messages will be created in the table AD_Message.

Message
Name Message Type Message Text Explanation Importance
HCMC_NoSetup Error "The default setup window must be filled out first" Used in HCMC_C_BParter_trg. Raised if an employee is inserted. If no defaults setup is done, no category can be set for the employee. Must have
HCMC_SameStartDate Error "There is already a period starting on the same day. Please check the selected data range." Used in HCMC_AddManager function Must have
HCMC_NoDelete Error "No record can be deleted from this tab." Must have
HCMC_WrongDataRange Error "The selected data range conflicts with other periods." Must have
HCMC_OneDefaultRecord Error "There is already one default setup record. Only one record is allowed." Must have
HCMC_NoempCategory Error "There is no employee category set in the default setup window." Must have
HCMC_SalaryRange Information "The current salary is not in the salary range. The salary range for this salary category is between x and y." Nice to have
HCMC_SalaryRange Information "As the organization has being changed the employees position and/or salary category had to be changed as well." Nice to have
HCMC_NotMarried Information "The current employees marital status is different to married" Nice to have
HCMC_NoManager Error "The current team has no manager assigned. It is not possible choose a team with no manager." Must have
HCMC_NoManagerNoconfirmation Error "The team can not be confirmed because it has no manager assigned." Must have
NoManagerInf Information "The current team has no manager assigned. It is not possible choose a team with no manager." Nice to have
HCMC_LinkedEmployees Error "The current record can not be deactivated because there are active employees linked to it." Nice to have
HCMC_LinkedSetup Error "The current record can not be deleted because the default setting of the human capital managements are linked to it." Must have
HCMC_ItemChange Information Something like: "Because of the recent change, some items has changed their value". When the organization is changed in the job information tab, the positions and salary categories may change as well. Just to advise this. Nice to have
ConstraintName Error There will be a message for each constraint added by the module Must have

Reference

The following items will be added to AD_Reference table. This values will be exported as part of the dataset.

Reference
Name Description
Marital Status Used in employee header
Employment Status Used in family member tab
Gender Used in employee header and family member tab
Dedication Used in job information tab
Status Used in employee header to set the employee to former employee or active employee.
Contact type Used in contact tab
Relation Used in family member tab
Education Level Used in employee header tab
Work Period Used in job information tab
Account type Used in bank information tab

Callout

The following callouts will be implemented. This document is still on draft, so perhaps this callouts will be implemented in the same callout file.

Callout
Name Changing Field Window - Tab Actions Importance
Callout_1 Position Employee - Job Information Update salary categories available. If possible, kept the previously selected one. Must have
Callout_2 Team Employee - Job Information Check if the team has a supervisor. If not, show an information message. If the record is saved, a trigger will show the corresponding error message. This is just a preventing message. Nice to have
Callout_3 First/Middle/Last name Employee header Know as field updated to the concatenation of previous fields. Only if the previous 'know as' was the concatenation of the previous values. Must have
Callout_4 Relation Family Member If is set to 'spouse' and the employee is not married show information message. Nice to have

Fields with display logic

Display logic
Name Field Window - Tab Logic Importance
jobinformation Standar hours Employee - Job Information Visible if dedication = part-time Must have
jobinformation Work Period Employee - Job Information Visible if dedication = part-time Must have
employee status Exit date Employee Visible if status = former-employee Must have

Trigger

The following triggers will be implemented. Some of this triggers will be implemented in the same trigger file.

Trigger
Table Launching action Action Performed Importance
C_BPartner Insert In the row being inserted: If it is an employee, set isCustomer=Y, set c_category and vendor attributes to the values set in the defaults window. If there is no values in the default value window, display message. Must have
HCMC_EmploymentInformation Insert / Modification
  • If the salary is not in the salary range display salary message
  • Add the currency symbol to the salary
Nice to have
HCMC_EmploymentInformation Modification If dedication turns from part time to full time: turn to null standard hours and work period. Must have
HCMC_EmploymentInformation Modify/Insert If the team, organization or position is changed, update the read only fields of the employee header. Must have
HCMC_EmploymentInformation Modify/Insert If the end date is set to null check whether is the current record or not. If not, do not allow this change. Must have
C_bp_category Delete Do not allow deletion of business partner category used in defaults window. Show message. Must have
C_BPartner Insert/Modification If exit date field is filled out, set this date as the 'end date' of the current record in HCMC_employmentInformation table and HCMC_Team_manager table. If there is a manager that ends being manager, set current manager of HCMC_Team to null. Must have
HCMC_job_information Delete Do not allow deletion of business partner category used in defaults window. Show message. Must have
HCMC_job_information Delete Do not allow to select a team with no current manager. Must have
HCMC_bank_account Insert Set Show IBAN or Show generic depending on selected account type. Must have

Java Processes

Function
Name Description Importance
Fun1_addmanager Java process linked to add manager button in team tab. WAD will create a popup with the input parameters of the function, which should be the manager, effective from and effective to. Must have
Fun2_makedefault Java process linked to make default button. It will uncheck the default check in the default record, if any, and will set the actual one to default. used in three windows: bank, contact, location. Must have
Fun3_complete Java process linked to complete button in job information tab. It will set the record to ready status and turn the previous ready record to historic status. Must have
Background Process Background process ran once a day to update fields. Must have

Other

Other items that should be taken into consideration.

Other
Name Description Importance
Validations Must have
Preferences Must have
Read only logic click complete button -> make records read only.
Field group Expense Reimbursement Information
Field group Employment Information

Role Management

Open Discussion Items

Retrieved from "http://wiki.openbravo.com/wiki/Projects:Humancapitalmanagement/Employee_Information_Management/Technical_Documentation"

This page has been accessed 5,647 times. This page was last modified on 8 June 2012, at 05:27. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.