Query to Request created from Mail and waiting for an Tech Reply.
Version : 14001
DB : PGSQL / MSSQL
OUTPUT :
SELECT wo.WORKORDERID AS "Request ID",
wo.TITLE AS "Subject",
ad.ORG_NAME AS "Account"
FROM WorkOrder wo
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID
LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID
LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID
INNER JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID WHERE (wo.ISPARENT='1') and mdd.modename = 'E-Mail' and wos.notificationstatus = 'REQ_REPLY'
wo.TITLE AS "Subject",
ad.ORG_NAME AS "Account"
FROM WorkOrder wo
LEFT JOIN ModeDefinition mdd ON wo.MODEID=mdd.MODEID
LEFT JOIN WorkOrderAccountMapping wam ON wo.WORKORDERID=wam.WORKORDERID
LEFT JOIN PortalAccounts port_acc ON wam.ACCOUNTID=port_acc.ACCOUNTID
LEFT JOIN AccountDefinition ad ON port_acc.ACCOUNTID=ad.ORG_ID
INNER JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID WHERE (wo.ISPARENT='1') and mdd.modename = 'E-Mail' and wos.notificationstatus = 'REQ_REPLY'