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 Implementer", Reviewer.name "Change Reviewer", Approver.name "Change Approver", cr.rolloutplan "Roll Out Plan",
cr.backoutplan "Back Out Plan",
cr.impactdesc "Impact Descritpion",
cr.checklist "Check List", LONGTODATE(cdd.starttime) "Downtime Start", LONGTODATE(cdd.endtime) "Downtime End", Csc.comments "Status comments" FROM ChangeDetails cdt LEFT JOIN (SELECT role.changeid, array_to_string(array_agg(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)implementer ON cdt.changeid=implementer.changeid LEFT JOIN (SELECT role.changeid, array_to_string(array_agg(us.first_name),',') AS name FROM changeroleusermapping ROLE LEFT JOIN aaauser us ON ROLE.userid=us.user_id WHERE ROLE.roleid=9 GROUP BY ROLE.changeid)Reviewer ON cdt.changeid=reviewer.changeid LEFT JOIN (SELECT role.changeid, array_to_string(array_agg(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)Approver ON cdt.changeid=Approver.changeid LEFT JOIN (SELECT role.changeid, array_to_string(array_agg(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)manager ON cdt.changeid=manager.changeid LEFT JOIN SDUser orgsd ON cdt.INITIATORID=orgsd.USERID LEFT JOIN AaaUser orgaaa ON orgsd.USERID=orgaaa.USER_ID LEFT JOIN SDUser ownsd ON cdt.TECHNICIANID=ownsd.USERID LEFT JOIN AaaUser ownaaa ON ownsd.USERID=ownaaa.USER_ID LEFT JOIN AaaUser cmDef ON cdt.CHANGEMANAGERID=cmDef.USER_ID LEFT JOIN changeresolution cr ON cdt.changeid=cr.changeid LEFT JOIN Changestatuscomments csc ON cdt.changeid=csc.changeid LEFT JOIN change_downtimedetails cdd ON cdt.changeid=cdd.changeid ORDER BY 1
New to ADSelfService Plus?
Related Articles
Change roles
SELECT chdt.changeid "Change ID", chdt.title "Title", orgaaa.first_name "Change Requester", ownaaa.first_name "Change Owner", ...Change details
SELECT chdt.changeid "Change ID", chdt.title "Title", Longtodate(chdt.createdtime) "Created Time", Longtodate(chdt.scheduledstarttime) "Scheduled Start Time", ...Query to show change planning details with total worklog hours
Working on Build: 14500 MSSQL: SELECT cdt.changeid "Change ID", cr.rolloutplan "Roll Out Plan", cr.backoutplan "Back Out Plan", cr.checklist "Check List", ...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 ...Automating change stage transition based on task completion
PURPOSE: To automatically move to the next change stage when all tasks are closed, using task custom function and task custom trigger. The attached script triggers upon the closure of each task, verifying whether all tasks within the current stage ...