Phase 4 – Employee Master Upload

The Employee Master Upload is the fourth and final phase of the CRS Data Loader workflow. This phase enables administrators to bulk-create employees, assign roles, map departments and designations, and configure jurisdiction boundaries for a given tenant.

The upload process follows the same structured validation model as previous phases:

1. HRMS API-Level Validation (Backend Service)

This ensures that only accurate, complete, and compliant employee data is inserted into the system.

1. Employee Master Template Overview

The Employee Master Template consists of a single worksheet containing the employee profile, role assignments, and jurisdiction configuration. All mandatory fields must be completed before upload. Optional fields are assigned system defaults if left empty.

Employee Master Template – Column Definitions

Column Name	Description	Mandatory	Example	Data Type
User Name*	Full name of the employee	Yes	John Doe	Text (A–Z, spaces)
Mobile Number*	Unique 10-digit mobile number	Yes	9876543210	Numeric (10 digits)
Password	Login password (default applied if empty)	No	MyPass@123	Text (≥8 chars)
Department Name*	Must match Department from Phase-3	Yes	WATER DEPARTMENT	Text (Dropdown)
Designation Name*	Must belong to Department	Yes	engineer	Text (Dropdown)
Role Names (comma-separated)*	Valid system role codes	Yes	GRO,EMPLOYEE	Text (multi-select)
Gender	Male/Female/Other	No	Male	Dropdown
Employee Status	Employment status	No	EMPLOYED	Dropdown
Employee Type	Type of employee	No	PERMANENT	Dropdown
Assignment From Date*	Start date of assignment	Yes	05-09-2024	Date (DD-MM-YYYY)
Date of Appointment*	Joining date	Yes	20-06-2024	Date (DD-MM-YYYY)
Hierarchy Type	Admin hierarchy type	No	ADMIN	Text
Boundary Type	Boundary type for jurisdiction	No	City	Text
Boundary Code	Boundary code assigned	No	PG.CITYA	Text

System-Generated Fields (Not Present in Excel)

The loader automatically generates:

Attribute	Logic	Example
Employee Code	Name converted to uppercase, spaces → underscores	JOHN_DOE
Tenant ID	Taken from upload session	pg.citya
User UUID	Generated by HRMS	1fda5623-448a-...
User Type	Always "EMPLOYEE"	EMPLOYEE
Active Flag	Always true	true
OTP Reference	System default	12345

---

2. Dynamic Template Generation (Jupyter Notebook)

The Data Loader provides a dynamic template generator to eliminate manual errors.

Steps in the Notebook

1. The Administrator enters the Target Tenant ID (e.g., pg.citya).

2. Notebook fetches:

   1. Departments from MDMS

   2. Designations from MDMS

   3. Roles from MDMS

   4. Boundary hierarchy from Boundary Service

3. The system generates a pre-validated Excel with dropdowns.

Template is saved as: templates/Employee_Master_Dynamic_<tenant>.xlsx

1. This ensures the template always aligns with the latest MDMS configuration.

---

3. Filling the Employee Master Template

Mandatory Fields – Validation Rules

Field	Validation
User Name	Text only; no symbols except spaces
Mobile Number	10 digits; must be unique
Department Name	Must match Phase 3 (case-sensitive)
Designation Name	Must belong to selected department
Role Names	Comma-separated; valid codes only
Dates	Format: DD-MM-YYYY

Optional Fields – Default Values

Field	Default
Password	eGov@123
Employee Status	EMPLOYED
Employee Type	PERMANENT
Hierarchy Type	ADMIN
Boundary Type	City
Boundary Code	Tenant code

---

4. Validation Layers

4.1 HRMS API-Level Validation

API endpoint: POST /egov-hrms/employees/_create

Validations include:

• Mobile number uniqueness (DB-level)

• Duplicate employee code

• Invalid jurisdiction boundary

• Invalid role assignment

• Missing or outdated MDMS references

5. Processing Employee Upload in Jupyter Notebook

Workflow Execution

Upload the filled Excel File. The file is saved under: upload/Employee_Master.xlsx

1. Notebook Processes Each Row

2. Converts department/designation names → codes

3. Converts roles → role objects

4. Generates employee code

5. Converts dates → Unix timestamps

6. Builds HRMS payload

7. Data Submission to HRMS

8. Sends a structured payload to /employees/_create

9. Captures row-wise success/failure

10. Status Update in Excel File

New columns are appended:

Column	Description
_STATUS	SUCCESS / FAILED / EXISTS
_STATUS_CODE	201 / 400 / 409 / 500
_ERROR_MESSAGE	Detailed message

---

6. Employee Upload Error Handling

Error Message	Reason
Department not found	Incorrect spelling or missing from Phase 3
Mobile number already exists	Duplicate in system
Role not found	Invalid role code
Invalid date format	Not in DD-MM-YYYY format
Boundary code invalid	Not matching Phase 2 hierarchy

Common Errors & MeaningError File Workflow

1. Download processed file

2. Review FAILED rows

3. Correct only failed records

4. Remove status columns

5. Re-upload the corrected file

The loader automatically skips successful rows.

7. Password Management Guidelines

• Default password applied if empty: eGov@123

• Custom password must meet security criteria:

• Min 8 characters

• 1 uppercase, 1 lowercase, 1 number, 1 special character

Password update limitations: After employee creation, updating the password via Excel is not supported. Use HRMS UI for password reset.

8. Role & Jurisdiction Assignment

Role Assignment

Roles define employee privileges.

Common roles include:

Code	Description
GRO	Grievance Routing Officer
CSR	Customer Service Representative
EMPLOYEE	Field/department staff

Multiple roles can be assigned using comma separation.

Jurisdiction Assignment

A jurisdiction maps a user to a boundary (city/ward/zone).

If no boundary is entered, the employee is assigned to the entire tenant boundary.