Contract Details

Contract Details





SELECT mcdt.Contractid "Contract ID",
       mcdt.CONTRACTNAME "Contract Name",
       mcdt.comments "Description",
       contractcategory.Categoryname "Contract Type",
       LONGTODATE(mcdt.CREATEDDATE) "Created Time",
       LONGTODATE(mcdt.FROMDATE) "From Date",
       LONGTODATE(mcdt.TODATE) "To Date",
       mcdt.TOTALPRICE "Total Price",
       aao.NAME "Maintenance Vendor Name",
       aaauser.FIRST_NAME "Contact Person",
       aaacontactinfo.LANDLINE "pHONE",
       aaacontactinfo.EMAILID "Email",
       cbyaau.FIRST_NAME "Contract Requester",
       cst.STATUSNAME "Contract Status",
       mcdt.support "Support Details",
       cns.days "Notify days",
       cnm.Mailid "mail id",
       Resources.RESOURCENAME "Asset Associated",
       sda.Attachmentname "Attachmentname" FROM MaintenanceContract mcdt
LEFT JOIN VendorDefinition vdn ON mcdt.MAINTENANCEVENDOR=vdn.VENDORID
LEFT JOIN SDOrganization aao ON vdn.VENDORID=aao.ORG_ID
LEFT JOIN SDOrgContactInfo ON aao.ORG_ID=SDOrgContactInfo.ORG_ID
LEFT JOIN SDOrgContactUser ON aao.ORG_ID=SDOrgContactUser.ORG_ID
LEFT JOIN AaaUser ON SDOrgContactUser.USER_ID=AaaUser.USER_ID
LEFT JOIN AaaContactInfo ON SDOrgContactInfo.CONTACTINFO_ID=AaaContactInfo.CONTACTINFO_ID
LEFT JOIN aaapostaladdress ON aao.ORG_ID=aaapostaladdress.POSTALADDR_ID
LEFT JOIN SDUser cby ON mcdt.CREATEDBY=cby.USERID
LEFT JOIN AaaUser cbyaau ON cby.USERID=cbyaau.USER_ID
LEFT JOIN ContractStatus cst ON mcdt.STATUSID=cst.STATUSID
LEFT JOIN ContractDetails ON mcdt.CONTRACTID=ContractDetails.CONTRACTID
LEFT JOIN Resources ON ContractDetails.RESOURCEID=Resources.RESOURCEID
LEFT JOIN contractcategory ON mcdt.categoryid=contractcategory.categoryid
LEFT JOIN ResourceLocation resLocation ON resources.RESOURCEID=resLocation.RESOURCEID
LEFT JOIN contractattachment ca ON mcdt.contractid=ca.contractid
LEFT JOIN sdeskattachment sda ON ca.attachmentid=sda.attachmentid
LEFT JOIN contractnotificationsettings cns ON mcdt.contractid=cns.contractid
LEFT JOIN contractnotificationmailids cnm ON mcdt.contractid=cnm.contractid
ORDER BY 1

      • Related Articles

      • How to auto-create request for contract expiry.

        This post describes the use of a python script to auto-create requests for contract expiry using Custom Schedules. Use case: If the contract going to expire in next 7 days, we can create a ticket with the details for the contract in ServiceDesk Plus, ...
      • Contract expire in next 30 days

        MSSQL SELECT mcdt.CONTRACTNAME "Contract Name", r.resourcename "Asset", longtodate(mcdt.todate) "Date" FROM MaintenanceContract mcdt LEFT JOIN contractdetails cd ON cd.contractid = mcdt.contractid LEFT JOIN resources r ON r.resourceid = cd.resourceid ...
      • Requester details

        This report is used to find the Requester complete details.  SELECT AaaUser.USER_ID, AaaUser.FIRST_NAME "FullName", AaaLogin.NAME "LoginName", AaaLogin.DOMAINNAME "Domain", AaaContactInfo.EMAILID "Email", DepartmentDefinition.DEPTNAME "Department", ...
      • Purchase Request details

        SELECT pr.Requestid "Requestid", pr.Subject "Subject", pr.Description "Description", longtodate(pr.Requesteddate) "Requesteddate", longtodate(pr.Daterequired) "Daterequired", longtodate(pr.createddate) "createddate", pr.Shipping_details ...
      • Software details

        SELECT systeminfo.workstationname             "Workstation",         Max(aaauser.first_name)                "User",         ( softwarelist.softwarename )          "Software_Name",         Max(softwaretype.softwaretype)         "Software_Type",  ...