Query to find Contact roles
All requests from their account and sub account
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='ADMIN'
All requests from their account (Primary Contact)
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='Manager'
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='User'
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='ADMIN'
All requests from their account (Primary Contact)
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='Manager'
SELECT aaauser.FIRST_NAME "Contact Name",org.NAME "Account Name",ucinfo.EMAILID "Contact Email",ucinfo.LANDLINE "Contact Phone",ucinfo.MOBILE "Contact Mobile",ucinfo.FAX "Contact Fax" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ON aaauser.USER_ID=user_contact.USER_ID LEFT JOIN AaaContactInfo ucinfo ON user_contact.CONTACTINFO_ID=ucinfo.CONTACTINFO_ID LEFT JOIN SDUser sduser ON aaauser.USER_ID=sduser.USERID LEFT JOIN Customer_Requester custreq ON sduser.USERID=custreq.REQUESTER_ID LEFT JOIN Customer cust ON custreq.CUSTOMER_ID=cust.CUSTOMER_ID LEFT JOIN AaaOrganization org ON cust.CUSTOMER_ID=org.ORG_ID LEFT JOIN DepartmentUser dep_user ON sduser.USERID=dep_user.USERID LEFT JOIN requester req on dep_user.departmentuserid=req.departmentuserid WHERE ((dep_user.DEPARTMENTID = 1) AND ( dep_user.USERTYPE = 'Contact')) AND (sduser.STATUS='ACTIVE') AND req.userstatus='User'