MBA Employee Sync Documentation

The RSC SQL Automation Engine runs your employee sync to update ARCHIBUS from source data. This document is a summary of your employee sync and related automations.

Data Source

Source Type: Excel spreadsheet is dropped into the folder

Folder: on Grouper (172.200.0.17) D:\CAFM\AFMDATA\Download\EmployeeFile

File Name: emp_list

Restriction: All past employees are in the file. Employees are only imported if their Position Status does not equal “Terminated” and their file number, first name, and last name are not null

Date Appended? Yes – once the file has been processed the processed file gets appended with the date and left in the folder.

Note: Field order matters on this report. Please provide RSC with a copy of the new report before putting it in the folder if any changes are required as the sync will need to be updated first.

Scheduled: Daily 3:00am

Log emailed to: notify@rsc2lc.com

Employee Sync

Master Function: EMSYNC

Data Path: emp_list.xls -> rsc_emsource table -> rsc_emdown table -> em table

Note: data follows this path as it is formatted for the appropriate field. Before running the emsource and emdown tables are cleared of all previous data. All data from the source is first pulled into the emsource table.

Scheduled: Checks daily at 3:00am for source file, runs only if file is found.

Log emailed to: notify@rsc2lc.com

EMSOURCE	EMDOWN	EM	Updated if changed in source?
Field1	Division	Dv_id	Yes
Field3	Department	Dp_id	Yes
Field8	managerdown	manager	Yes
Field5	jobtitledown	title	Yes
Field6	Date_hire	Date_hired	Yes
Field7	emstddown	Em_std	yes
Field9	statusdown	status	yes
Field10	emaildown	Email, net_user_name	yes
		Em_number*	No
Field12	firstname	Name_first	No
Field13	Lastname	Name_last	No
Field14	Phonedown	Phone	Yes
Field15	Name_pref	Name_pref	Yes
Field12,Field13	Fullname	Name_full	No
Field13,Field12 Field 11	Combined Name	Em_id	No
		Update_type (will display “NEW” when new, and “MODIFIED” when modified)
		Date_refresh (current date)
		Em_seat_status (UNLOCATED on insert)

*Field11 employee number is used for mapping managers to employees but is not brought into Archibus, a new number is generated.

New Hire Moves Created

When a new employee is added to the database (update_type = new) a new hire move is created for them.

Scheduled: part of the employee sync

Function: EM_TO_MO_NEW

Em	Mo	Updated if changed in source?
Em_id	Em_id	No
Date_hired	Date_to_perform	Yes
Dv_id	To_dv_id	Yes
Dp_id	To_dp_id	Yes
	Move_type	New Hire

User Records Created

When a new employee is added to the database (update_type = new) a user record is created for them.

Master Function: USERS_SYNC

Scheduled: Daily 3:30am

Log emailed to: notify@rsc2lc.com

Em	Afm_users	Updated if changed in source?
Net_user_name	User_name	Yes*
Email	Email	Yes
	User_level set to 0	Insert only
	User_pwd set to PASSWORD1	Insert only
	Role_name set to MBA USER	Insert only
	Home_page set to navigator-details.axvw	Insert only
	Group_1 (spaceview security group) set to TYPGRP	Insert only
	Sync_status set to 1 (update)	Insert only

Note on “Insert Only” – these fields are populated when the record is created. If they are changed the automation does not update them back.

*If the employee’s email changes, this will update their net_user_name and email for their login as well. There is an email notification configured to notify MBA ARCHIBUS admins Cory and Travis when this occurs so they can determine if the user needs to be notified of their new login.

Employee Deletion

Deletion criteria: employee is no longer in sync file with a first name, last name, file number, or position status is “terminated” – this is referred to as “not found in source file”.

Manual Employees: Sync ignores all employees who have a sync mode of “no update”.

Deletion action:

1. Not in source first time sync runs: update type set to “delete”
2. Not in source second time sync runs: update type set to “deleted”
3. Not in source third time sync runs: employee is archived to the hem (historical employee) table and removed from the employee table.

Notification or approval for deletion: None

Leaving Moves Created

When a new employee is added to the database (update_type = new) a leaving move is created for them.

Scheduled: part of the employee sync

Function: EM_TO_MO_LEAVE

Em	Mo	Updated if changed in source?
Em_id	Em_id	No
Date_hired	Date_to_perform	Yes
	Move_type	Leaving

Craftsperson Email Updates

An em_id field has been added to the craftsperson table (and define craftsperson web central view) to link the craftsperson to the employee. An automation runs to update the craftsperson email to match the employee email if the employee email ever changes.

Scheduled: Daily 5am

SQL Automation Name: CRAFTS_LINK

Custom Field: rsc-ab-cf-edit.axvw

Division/department Updates

New Division/Department: If a division or department in the employee source (field 1 and field 3) is new it will be inserted into the dv and dp tables.

• Approval required for insertion: no

Updated Division/Department Name: If the name of a division of a department changes (field 2 and field 4) it will be updated in the dv and dp tables.

• Approval required for update: no

Employee Standard Updates

New Employee Standard: If an employee standard the employee source (field 7) is new it will be inserted into the emstd table.

• Approval required for insertion: no

Was this helpful?

YesNo

0 / 0