HRM-10/db/Script/EmpHistoricalData_DB_Changes.sql

97 lines
4.3 KiB
MySQL
Raw Permalink Normal View History

2026-11-03 13:06:00 +06:00
/* =====================================================================
EmpHistoricalData_DB_Changes.sql
Historical Career Data Upload
(Manage Employee > Interface > Historical Career Data Upload,
/payroll/career-and-profile/historical-career-data-upload)
One table. It holds the rows of the "Historical Data" Excel sheet as
they were uploaded, so that past career data can be kept, reviewed and
deleted row by row.
Why a new table rather than EmpLifeCycle:
- EmpLifeCycle is the live career history that payroll, arrear and
salary processing read. This screen must be able to delete a single
uploaded row, which must never touch those records.
- The lookup columns (Designation, Division, Grade, Cost-Center, ...)
are stored as the uploaded text, not as resolved master-data IDs,
so no uploaded row is ever rejected or silently changed because a
name does not match a master record.
Notes:
- EmpHistoricalDataID is generated by the application (tc.GenerateID),
so there is no IDENTITY column - adding one would break that pattern.
- No unique key over EmployeeNo / EffectDate / EventType: the same
employee legitimately has many historical rows, and repeated rows
must not be rejected.
- EmployeeID is a best-effort resolution of EmployeeNo and stays NULL
when the employee number does not match; the row is still kept.
- Re-runnable: the table and its index are created only if missing.
- Run once per database, before deploying the matching code.
===================================================================== */
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
/* ---------- dbo.EmpHistoricalData ---------- */
IF OBJECT_ID(N'dbo.EmpHistoricalData', N'U') IS NULL
BEGIN
CREATE TABLE dbo.EmpHistoricalData (
EmpHistoricalDataID INT NOT NULL, -- application-generated
EmployeeID INT NULL, -- resolved from EmployeeNo, best effort
EmployeeNo NVARCHAR(50) NULL,
Name NVARCHAR(250) NULL,
EffectDate DATETIME NULL,
EventType NVARCHAR(250) NULL,
Designation NVARCHAR(250) NULL,
EmployeeType NVARCHAR(250) NULL,
Division NVARCHAR(250) NULL,
Department NVARCHAR(250) NULL,
Section NVARCHAR(250) NULL,
SubSection NVARCHAR(250) NULL,
Grade NVARCHAR(250) NULL,
CostCenter NVARCHAR(250) NULL,
Location NVARCHAR(250) NULL,
SubLocation NVARCHAR(250) NULL,
PFMember NVARCHAR(50) NULL,
BasicSalary FLOAT NULL,
GrossSalary FLOAT NULL,
PayrollTypeID INT NULL, -- scoping key, as elsewhere in payroll
CreatedBy INT NOT NULL,
CreatedDate DATETIME NOT NULL,
ModifiedBy INT NULL,
ModifiedDate DATETIME NULL,
CONSTRAINT PK_EmpHistoricalData PRIMARY KEY (EmpHistoricalDataID)
);
END;
GO
/* Supports the "Uploaded Data" listing, which reads one payroll type. */
IF OBJECT_ID(N'dbo.EmpHistoricalData', N'U') IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM sys.indexes
WHERE object_id = OBJECT_ID(N'dbo.EmpHistoricalData')
AND name = N'IX_EmpHistoricalData_PayrollType')
BEGIN
CREATE NONCLUSTERED INDEX IX_EmpHistoricalData_PayrollType
ON dbo.EmpHistoricalData (PayrollTypeID, EmployeeNo, EffectDate);
END;
GO
/* ---------------------------------------------------------------------
MANUAL STEP - menu entry
The menu tree comes from Menu.xml (Authentication > Configuration >
Menu), not from this script. HRM.UI/ClientApp/src/assets/resource/Menu.xml
now carries the node
<Menu key="2.3.7" name="Historical Career Data Upload"
url="/payroll/career-and-profile/historical-career-data-upload"
parentkey="2.3" .../>
Re-upload that Menu.xml and grant the permission to the roles that need
the screen, the same way any other new screen is published.
--------------------------------------------------------------------- */