Project and Portfolio Management Practitioners Forum
cancel

Change request type and bulk-remove users from a group

Highlighted
Sascha Mohr
Acclaimed Contributor.

Change request type and bulk-remove users from a group

Hi.
I need to split a directory of requests (one request type) into two (two request types); would it be enough to update the request_type_id in the three request tables (kcrt_requests, kcrt_req_header_details, kcrt_request_details) for the requests to be moved to the new directory type?
The next step will be to split the security group used to access the RT in two. I want to delete the relevant entries in knta_user_Security. Would that have side effects?
Regards
Sascha
6 REPLIES
Mahen M
Acclaimed Contributor.

Re: Change request type and bulk-remove users from a group

Does the Request type has any field groups? then you have to update the relevent FG's Tables.

Does the Request has any sub types?

Does there is any change in Workflow?

If you maintain any transaction history then update KCRT_TRANSACTIONS

This is a risky item, there are more chances for the loss of Information. Good Luck.

Regards,
Mahendran M
Sascha Mohr
Acclaimed Contributor.

Re: Change request type and bulk-remove users from a group

Thanks for your input, I would have missed the entries in kcrt_transactions, fortunately the are no field groups used.
Sascha Mohr
Acclaimed Contributor.

Re: Change request type and bulk-remove users from a group

Seems it worked.
Mahen M
Acclaimed Contributor.

Re: Change request type and bulk-remove users from a group

If Possible can you share the Base Scripts?

Regards,
Mahendran M
Sascha Mohr
Acclaimed Contributor.

Re: Change request type and bulk-remove users from a group

sure:

-- old RT is 30993 new RT is 31493; parameter1 is sth like a location code

UPDATE kcrt_requests SET request_type_id = 31493 WHERE request_id IN (SELECT REQUEST_ID FROM kcrt_req_header_details WHERE request_type_id = 30993 AND parameter1 LIKE 'DE%' AND parameter1 NOT IN ('DE001','DE092','DEPDL'));
UPDATE kcrt_req_header_details SET request_type_id = 31493 WHERE request_id IN (SELECT REQUEST_ID FROM kcrt_req_header_details WHERE request_type_id = 30993 AND parameter1 LIKE 'DE%' AND parameter1 NOT IN ('DE001','DE092','DEPDL'));
UPDATE kcrt_request_details SET request_type_id = 31493 WHERE request_id IN (SELECT REQUEST_ID FROM kcrt_req_header_details WHERE request_type_id = 31493 AND parameter1 LIKE 'DE%' AND parameter1 NOT IN ('DE001','DE092','DEPDL'));
UPDATE kcrt_transactions SET request_type_id = 31493 WHERE request_id IN (SELECT REQUEST_ID FROM kcrt_req_header_details WHERE request_type_id = 31493 AND parameter1 LIKE 'DE%' AND parameter1 NOT IN ('DE001','DE092','DEPDL'));
COMMIT;

-- remove users from the security group that is used for accessing the old RT (they are already in the new one 102781)

DELETE FROM knta_user_Security
WHERE SECURITY_GROUP_ID = 100322
AND user_id IN
(
SELECT user_id
FROM knta_users_v
WHERE user_id IN (
SELECT USER_ID FROM knta_user_Security WHERE SECURITY_GROUP_ID = 100322
INTERSECT
SELECT USER_ID FROM knta_user_Security WHERE SECURITY_GROUP_ID = 102781
)
AND enabled_flag = 'Y'
);
COMMIT;
Mahen M
Acclaimed Contributor.

Re: Change request type and bulk-remove users from a group

Thank you.

Regards,
Mahendran M