Here is a SQL Server stored procedure which will make the current project manager, the collaboration manager on the project. I believe this was originally coded for Oracle by Niku Prof. Services. If any of you happens to refactor the code, please publish it for the benefit of the community.
Important : Run and verify on a test environment before doing it in production.
Usage :
0. Create the stored procedure OPTUS_MAKE_COLLAB_MANAGER_SP (see complete code below)
1. Create a new table called ch_temp_sp_proj with a column named project_code - datatype - nvarchar, length - 100
2. insert project_code into the table.
(e.g)
insert into ch_temp_sp_proj (project_code) values
('200700001118')
3. execute the following sql.
begin
declare @v_ugid NUMERIC
exec OPTUS_MAKE_COLLAB_MANAGER_SP @v_ugid out
end
---- Stored procedure code ----
create PROCEDURE [niku].[OPTUS_MAKE_COLLAB_MANAGER_SP] (@v_ugid NUMERIC OUT
)
AS
-- -------------------------------------------------------- */
-- XOG, when loading new projects, does not make the stated
-- project manager also the collaboration manager. This
-- script fixes that.
--
-- 7/10/2004 Steve Vong
-- 21/10/2004 Schedule job parameters added
-- 1/10/2005 Chummar Maly - SQLServer Port
-- -------------------------------------------------------- */
DECLARE PROJLISTCRSR CURSOR FOR
SELECT P2.PRID, P2.MANAGER_ID
FROM PRJ_PROJECTS P2,
SRM_PROJECTS P1
WHERE P1.ID = P2.PRID
AND p1.unique_name
in (select project_code
from ch_temp_sp_proj
)
DECLARE
@v_groupid NUMERIC,
@v_groupcd VARCHAR(40),
@v_dummy NUMERIC,
@v_managerid
NUMERIC,
@v_projectid
NUMERIC
-- AND TO_CHAR(CREATED_DATE,'YYYYMMDD') = TO_CHAR(SYSDATE,'YYYYMMDD'); /* Only today's new projects */
CLOSE PROJLISTCRSR
OPEN PROJLISTCRSR
FETCH NEXT FROM PROJLISTCRSR INTO @v_projectid,
@v_managerid
WHILE @@FETCH_STATUS=0
BEGIN
/* First make the user a participant */
SELECT @v_groupcd = 'CLB_PROJECT_MEMBERS'+rtrim(ltrim(str(@v_projectid))) FROM DUAL
SELECT @v_groupid = ID
FROM CMN_SEC_GROUPS
WHERE GROUP_CODE = @v_groupcd
PRINT 'Group ID '+str(@v_groupid)
IF NOT EXISTS (
SELECT ID FROM CMN_SEC_USER_GROUPS
WHERE USER_ID = @v_managerid
AND GROUP_ID = @v_groupid )
BEGIN
IF @v_managerid IS NOT NULL
BEGIN
EXEC CMN_SEC_USER_GROUPS_INS_ID_SP @v_managerid, @v_groupid, 1, @v_ugid OUT
END
END
/* Now make the user a project manager */
SELECT @v_groupcd='CLB_PROJECT_MANAGERS'+rtrim(ltrim(str(@v_projectid))) FROM DUAL;
SELECT @v_groupid = ID
FROM CMN_SEC_GROUPS
WHERE GROUP_CODE = @v_groupcd
IF NOT EXISTS (
SELECT ID FROM CMN_SEC_USER_GROUPS
WHERE USER_ID = @v_managerid
AND GROUP_ID = @v_groupid )
BEGIN
IF @v_managerid IS NOT NULL
BEGIN
EXEC CMN_SEC_USER_GROUPS_INS_ID_SP @v_managerid, @v_groupid, 1, @v_ugid OUT
END
END
FETCH NEXT FROM PROJLISTCRSR INTO @v_projectid,
@v_managerid
END
CLOSE PROJLISTCRSR
GO