97 lines
4.3 KiB
Transact-SQL
97 lines
4.3 KiB
Transact-SQL
/* =====================================================================
|
|
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.
|
|
--------------------------------------------------------------------- */
|