Query to get Asset contract and its details (MSSQL & PGSQL)

Query to get Asset contract and its details (MSSQL & PGSQL)

Tested in builds from PGSQL (14300) or MSSQL (14306)


SELECT mcdt.Contractid "URL Contract ID",
       mcdt.customcontractid "Custom contractid in details page",
       mcdt.CONTRACTNAME "Contract Name",
      aao.NAME "Maintenance Vendor Name",
       LONGTODATE(mcdt.FROMDATE) "From Date",
       LONGTODATE(mcdt.TODATE) "To Date",
       mcdt.TOTALPRICE "Total Price",
       aao.NAME "Maintenance Vendor Name",       
       cst.STATUSNAME "Contract Status",
       LONGTODATE(mcdt.CREATEDDATE) "Created Time",  
cbyaau.FIRST_NAME "Contract Requester",        
       cadf.udf_char1 "Addition 1",
cadf.udf_long1 "Addition numeric",
ad.ORG_NAME AS "Account Name",
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 Contract_Fields cadf ON mcdt.CONTRACTID=cadf.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 
left JOIN EscalateToMediator ON mcdt.ESCALATETOID=EscalateToMediator.ESCALATETOID 
LEFT JOIN EscalateToN ON EscalateToMediator.ESCALATETOID=EscalateToN.ESCALATETOID 
LEFT JOIN AaaUser aaa1 ON EscalateToN.USERID=aaa1.USER_ID
INNER JOIN ContractAccountmapping camp ON mcdt.contractid=camp.contractid 
INNER JOIN AccountDefinition ad ON camp.accountid=ad.org_id 
INNER JOIN ContractAccountMapping ON mcdt.CONTRACTID=ContractAccountMapping.CONTRACTID 
ORDER BY 1


Note -  cadf.udf_char1 "Addition 1", and cadf.udf_long1 "Addition numeric", are the syntax to get contract additional fields. You need to pass your contract additional fields UDF values to it. change udf_char1 and udf_long1 as per your table value. Addition 1 and Addition numeric are the naming convention , you can change it according to your need.

You can get the UDF values using the query -> select * from Contract_Fields


        New to ADManager Plus?

          New to ADSelfService Plus?