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 ADManager Plus?

          New to ADSelfService Plus?

          Start your free trial

            • 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 ...

            • Update Change Roles using Change Custom Function

              This is a sample Deluge script to set Line Manger field. When a Change Request is created, ChangeRequester's reporting_to user is set as the Line Manager automatically, using Change Custom Triggers. This is achieved by submitting a update request to ...

            • 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, ...

            • 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 --> ...

            • Technician Roles

              This report is used to get the roles of the technicians. For version 10.5 and below SELECT au.FIRST_NAME "Technician", (ar.NAME) "Role" FROM HelpdeskCrew hdc LEFT JOIN SDUser sd ON hdc.TECHNICIANID=sd.USERID LEFT JOIN AaaLogin al ON ...

              Related Products