Change roles

Change roles




SELECT chdt.changeid               "Change ID", 
       chdt.title                  "Title", 
       orgaaa.first_name           "Change Requester", 
       ownaaa.first_name           "Change Owner", 
       cmDef.first_name            "Change Manager", 
       stageDef.displayname        "Stage", 
       statusDef.statusdisplayname "Status", 
       implementer.NAME            "Change Implementer", 
       line.NAME                   "Change line manager", 
       rev.NAME                    "Change  Reviewer", 
       app.NAME                    "Change  Approver" FROM   changedetails chdt 
       LEFT JOIN sduser orgsd 
              ON chdt.initiatorid = orgsd.userid 
       LEFT JOIN aaauser orgaaa 
              ON orgsd.userid = orgaaa.user_id 
       LEFT JOIN sduser ownsd 
              ON chdt.technicianid = ownsd.userid 
       LEFT JOIN aaauser ownaaa 
              ON ownsd.userid = ownaaa.user_id 
       LEFT JOIN change_stagedefinition stageDef 
              ON chdt.wfstageid = stageDef.wfstageid 
       LEFT JOIN change_statusdefinition statusDef 
              ON chdt.wfstatusid = statusDef.wfstatusid 
       LEFT JOIN aaauser cmDef 
              ON chdt.changemanagerid = cmDef.user_id 
       LEFT JOIN (SELECT role.changeid, 
                         Max(us.first_name) AS NAME 
                  FROM   changeroleusermapping ROLE 
                         LEFT JOIN aaauser us 
                                ON ROLE.userid = us.user_id 
                  WHERE  ROLE.roleid = 7 
                  GROUP  BY ROLE.changeid)implementer 
              ON chdt.changeid = implementer.changeid 
       LEFT JOIN (SELECT role.changeid, 
                         Max(us.first_name) AS NAME 
                  FROM   changeroleusermapping ROLE 
                         LEFT JOIN aaauser us 
                                ON ROLE.userid = us.user_id 
                  WHERE  ROLE.roleid = 6 
                  GROUP  BY ROLE.changeid)line 
              ON chdt.changeid = line.changeid 
       LEFT JOIN (SELECT role.changeid, 
                         Max(us.first_name) AS NAME 
                  FROM   changeroleusermapping ROLE 
                         LEFT JOIN aaauser us 
                                ON ROLE.userid = us.user_id 
                  WHERE  ROLE.roleid = 8 
                  GROUP  BY ROLE.changeid)rev 
              ON chdt.changeid = rev.changeid 
       LEFT JOIN (SELECT role.changeid, 
                         Max(us.first_name) AS NAME 
                  FROM   changeroleusermapping ROLE 
                         LEFT JOIN aaauser us 
                                ON ROLE.userid = us.user_id 
                  WHERE  ROLE.roleid = 5 
                  GROUP  BY ROLE.changeid)app 
              ON chdt.changeid = app.changeid 


                  New to ADSelfService Plus?

                    • Related Articles

                    • How to automatically assign users to different roles in a change request.

                      This script is applicable only for builds prior to 11138. This is a sample python script to read the value in the Change Owner field, when a Change Request is created and set him/her as the Change Implementer automatically, using Change Custom ...
                    • Risk matrix for Change using Custom module

                      UseCase: Risk need to be calculated through Risk matrix (similar to Priority matrix in the request module) Steps to configure: Configure custom module as in the attached screenshot. 1. Navigate to Admin --> Developer space --> Custom module --> ...
                    • How to assign CAB members to a change request automatically.

                      Note:  Attached script will not work beyond 11138 SDP Version. ​ This is a sample script to add CAB Members to a Change Request through Change Custom Triggers.The CAB Members can only be added manually to the Change Request ,under the Approval Tab, ...
                    • Change Submission and Planning stage details (PGSQL)

                      Tested in build PGSQL (14300) SELECT cdt.changeid "Change Request ID", orgaaa.FIRST_NAME AS "Change Requester", ownaaa.FIRST_NAME AS "Change Owner", cmDef.FIRST_NAME AS "Change Manager", manager.name "Line Manager", implementer.name "Change ...
                    • Risk Matrix for Change using Custom Module

                      Overview: A risk matrix helps in identifying risks based on how likely they are to happen and how serious their impact could be. This makes it easier to decide which risks need urgent attention and which ones can be watched over time. It helps manage ...