Description in approval requests
Hello, I have this query in MSSQL that reports all approvals and the approval details. Is there a field that could be added that shows the original description when the approval was created and sent to the approver? We go through a lot of audits, and the approvals work well, but there is no context as to what each approval is for. I know it is captured in the Conversations, but can't find a good way to pull that detail into a report.
We are on SDP 9117 with MSSQL
Thank you.
Dave
SELECT wo.WORKORDERID "Request ID",std.STATUSNAME "Request Status", wo.TITLE "Subject",scd.NAME "Subcategory",longtodate(wo.COMPLETEDTIME) "Completed Time",rtdef.NAME "Request Type",asd.stagename "Stage name",ApprovalDetails.EMAIL "Approver Email",
LONGTODATE(ApprovalStage.SENT_DATE) "Approval Sent date",
LONGTODATE(ApprovalDetails.ACTION_DATE) "Approved Date",
ApprovalStatusDefinition.STATUSNAME "Approval Status",
ApprovalDetails.COMMENTS "Comments" FROM WorkOrder wo LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID JOIN ApprovalStageMapping ON wo.WORKORDERID=ApprovalStageMapping.WORKORDERID
LEFT JOIN ApprovalDetails ON ApprovalStageMapping.APPROVAL_STAGEID=ApprovalDetails.APPROVAL_STAGEID
LEFT JOIN ApprovalStage ON ApprovalDetails.APPROVAL_STAGEID=ApprovalStage.APPROVAL_STAGEID
LEFT JOIN approvalstatusdefinition ON ApprovalDetails.STATUSID=ApprovalStatusDefinition.STATUSID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
left join approvalstagedefinition asd on asd.STAGEID=ApprovalDetails.STAGEID WHERE ((std.STATUSNAME != N'Closed' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (std.STATUSNAME IS NULL)) AND wo.ISPARENT='1' order by 8
New to ADSelfService Plus?