Prerequisites |
| Roles, Projects |
SQL Script forAssigning multiple roles to a project
-- =====================================================================
-- Script: Copy Multiple Roles to Project with Activities and Sub-Activities
-- Purpose: Insert roles from Role Master to Project Roles along with all
-- related activities and sub-activities
-- =====================================================================
DECLARE @ProjectID INT = 6087; -- Change this to your Project ID
DECLARE @LoginEmpCode NVARCHAR(50) = 'ADMIN0001'; -- Change this to logged-in user code
DECLARE @CreatedBy NVARCHAR(50) = @LoginEmpCode;
DECLARE @CreatedDate DATETIME = GETDATE();
-- =====================================================================
-- Step 1: Define the roles to copy (provide multiple RoleMasterIDs)
-- =====================================================================
DECLARE @RolesToCopy TABLE (
RoleMasterId INT,
RoleName NVARCHAR(255),
PerDayCost DECIMAL(18,2),
CurrencyCode NVARCHAR(10),
ReferenceCode NVARCHAR(50)
);
-- Insert the roles you want to copy
INSERT INTO @RolesToCopy (RoleMasterId, RoleName, PerDayCost, CurrencyCode, ReferenceCode)
SELECT
RoleMasterId,
RoleMasterName,
ISNULL(PerDayCost, 0),
ISNULL(CurrencyCode, ''),
ISNULL(ReferenceCode, '')
FROM HRMS_ROLE_MASTER
WHERE RoleMasterId IN (1183,1181,1180,1179,1178,1177,1175,1158,23,12); -- Change these to your Role Master IDs
-- =====================================================================
-- Step 2: Insert Roles into Project Roles (if not already exists)
-- =====================================================================
DECLARE @RoleCounter TABLE (
RoleMasterId INT,
ProjectRoleID INT
);
INSERT INTO HRMS_PROJECT_ROLES (ProjectID, RoleID, RoleName, PerDayCost, CurrencyCode, ReferenceCode, IsSystem, StatusID, CreatedBy, InsertDateTime)
OUTPUT inserted.ProjectRoleID, inserted.RoleID INTO @RoleCounter
SELECT
@ProjectID,
r.RoleMasterId,
r.RoleName,
r.PerDayCost,
r.CurrencyCode,
r.ReferenceCode,
0,
1,
@CreatedBy,
@CreatedDate
FROM @RolesToCopy r
WHERE NOT EXISTS (
SELECT 1 FROM HRMS_PROJECT_ROLES pr
WHERE pr.ProjectID = @ProjectID
AND pr.RoleName = r.RoleName
);
-- =====================================================================
-- Step 3: Log the inserted Project Roles
-- =====================================================================
INSERT INTO HRMS_PROJECT_ROLES_LOG
SELECT * FROM HRMS_PROJECT_ROLES
WHERE ProjectID = @ProjectID
AND CreatedBy = @CreatedBy
AND InsertDateTime = @CreatedDate;
-- =====================================================================
-- Step 4: Insert Main Activities for each Role
-- =====================================================================
INSERT INTO HRMS_PROJECT_ACTIVITY (ProjectID, ActivityID, ActivityName, ParentActivityID, RoleMasterId, ProjectRoleID, Type, StatusID, CreatedBy, InsertDateTime)
SELECT
@ProjectID,
am.ActivityID,
am.ActivityName,
0,
am.RoleMasterId,
pr.ProjectRoleID,
1,
1,
@CreatedBy,
@CreatedDate
FROM HRMS_ACTIVITYMASTER am
INNER JOIN @RolesToCopy r ON am.RoleMasterId = r.RoleMasterId
INNER JOIN HRMS_PROJECT_ROLES pr ON pr.ProjectID = @ProjectID
AND pr.RoleName = r.RoleName
WHERE ISNULL(am.ParentActivityID, 0) = 0
AND NOT EXISTS (
SELECT 1 FROM HRMS_PROJECT_ACTIVITY pa
WHERE pa.ProjectID = @ProjectID
AND pa.ActivityID = am.ActivityID
AND pa.RoleMasterId = am.RoleMasterId
);
-- =====================================================================
-- Step 5: Insert Sub-Activities for each Main Activity
-- =====================================================================
INSERT INTO HRMS_PROJECT_ACTIVITY (ProjectID, ActivityID, ActivityName, ParentActivityID, RoleMasterId, ProjectRoleID, Type, StatusID, CreatedBy, InsertDateTime)
SELECT
@ProjectID,
sam.ActivityID,
sam.ActivityName,
pma.ProjectActivityID,
sam.RoleMasterId,
pr.ProjectRoleID,
1,
1,
@CreatedBy,
@CreatedDate
FROM HRMS_ACTIVITYMASTER sam
INNER JOIN @RolesToCopy r ON sam.RoleMasterId = r.RoleMasterId
INNER JOIN HRMS_PROJECT_ROLES pr ON pr.ProjectID = @ProjectID
AND pr.RoleName = r.RoleName
INNER JOIN HRMS_ACTIVITYMASTER am ON am.ActivityID = sam.ParentActivityID
INNER JOIN HRMS_PROJECT_ACTIVITY pma ON pma.ProjectID = @ProjectID
AND pma.ActivityID = am.ActivityID
AND pma.RoleMasterId = am.RoleMasterId
WHERE ISNULL(sam.ParentActivityID, 0) != 0
AND NOT EXISTS (
SELECT 1 FROM HRMS_PROJECT_ACTIVITY pa
WHERE pa.ProjectID = @ProjectID
AND pa.ActivityID = sam.ActivityID
AND pa.ParentActivityID = pma.ProjectActivityID
AND pa.RoleMasterId = sam.RoleMasterId
);
-- =====================================================================
-- Step 6: Log all inserted Project Activities
-- =====================================================================
INSERT INTO HRMS_PROJECT_ACTIVITY_LOG
SELECT * FROM HRMS_PROJECT_ACTIVITY
WHERE ProjectID = @ProjectID
AND CreatedBy = @CreatedBy
AND InsertDateTime = @CreatedDate;
-- =====================================================================
-- Step 7: Display Results
-- =====================================================================
PRINT 'Operation Completed Successfully!';
PRINT '';
PRINT 'Project Roles Inserted:';
SELECT * FROM HRMS_PROJECT_ROLES WHERE ProjectID = @ProjectID AND CreatedBy = @CreatedBy AND InsertDateTime = @CreatedDate;
PRINT '';
PRINT 'Activities Inserted:';
SELECT * FROM HRMS_PROJECT_ACTIVITY WHERE ProjectID = @ProjectID AND CreatedBy = @CreatedBy AND InsertDateTime = @CreatedDate;