Doesnt work in PGSQL (14300)
ERROR: relation "workorder_threaded" does not exist
SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name "Approver", asd.statusname "Approval Status" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON
td.USERID=ti.USER_ID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN ImpactDefinition id ON wos.IMPACTID=id.IMPACTID LEFT JOIN ApprovalStageMapping appsc on appsc.WORKORDERID=wo.WORKORDERID LEFT JOIN ApprovalStage apps on apps.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN ApprovalDetails aaad on aaad.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN aaauser appsau on appsau.user_id=aaad.approverid LEFT JOIN ApprovalStatusDefinition asd on asd.STATUSID=aaad.STATUSID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wot.THD_WOID=wot.WORKORDERID) and (wo.IS_CATALOG_TEMPLATE='TRUE') and asd.statusname='pending approval' and appsau.first_name='Jeniffer Doe' order by 6
Note: Replace the highlighted name with the approver's name to get the desired results.
Same query excluding those requests which are already approved:
SELECT wo.WORKORDERID "Request ID",aau.FIRST_NAME "Requester",wo.TITLE "Subject",ti.FIRST_NAME "Technician",
longtodate(wo.CREATEDTIME) "Created Time",appsau.first_name "Approver", asd.statusname "Approval Status" FROM WorkOrder_Threaded wot INNER JOIN WorkOrder wo ON wot.WORKORDERID=wo.WORKORDERID LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON
td.USERID=ti.USER_ID LEFT JOIN RequestTypeDefinition rtdef ON wos.REQUESTTYPEID=rtdef.REQUESTTYPEID LEFT JOIN PriorityDefinition pd ON wos.PRIORITYID=pd.PRIORITYID LEFT JOIN ImpactDefinition id ON wos.IMPACTID=id.IMPACTID LEFT JOIN ApprovalStageMapping appsc on appsc.WORKORDERID=wo.WORKORDERID LEFT JOIN ApprovalStage apps on apps.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN ApprovalDetails aaad on aaad.APPROVAL_STAGEID=appsc.APPROVAL_STAGEID LEFT JOIN aaauser appsau on appsau.user_id=aaad.approverid LEFT JOIN ApprovalStatusDefinition asd on asd.STATUSID=aaad.STATUSID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID WHERE (wot.THD_WOID=wot.WORKORDERID) and (wo.IS_CATALOG_TEMPLATE='TRUE') and asd.statusname='pending approval' AND wos.appr_statusid!=2 AND appsau.first_name='administrator' order by 6