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 ...
                    • 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 ...
                    • 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 ...
                    • Update Change Roles based on Support Group

                      Requirement: Set Change Approver based on Support group selected Steps to configure: Download the attached scripts. Navigate to Admin > Developer Space > Change Custom Function. Copy and paste the content of the attached file ...
                    • 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, ...