Query to show Contract details with assets associated (PGSQL)
Tested in PGSQL build (14300)
SELECT mcdt.customcontractid "Contract ID", mcdt.CONTRACTNAME AS "Contract Name", mcdt.customcontractid "Contract ID", contractcategory.Categoryname "Contract Type", LONGTODATE(mcdt.CREATEDDATE) AS "Created Time", ad.ORG_NAME AS "Account", rs.RESOURCENAME AS "Asset Name", product.COMPONENTNAME AS "Product", rs.SERIALNO AS "Org Serial Number" FROM MaintenanceContract mcdt LEFT JOIN Contract_Fields cadf ON mcdt.CONTRACTID=cadf.CONTRACTID LEFT JOIN VendorDefinition vdn ON mcdt.MAINTENANCEVENDOR=vdn.VENDORID LEFT JOIN SDOrganization aao ON vdn.VENDORID=aao.ORG_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 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 LEFT JOIN contractcategory ON mcdt.categoryid=contractcategory.categoryid
LEFT JOIN Contractdetails cd ON mcdt.contractid=cd.contractid LEFT JOIN Resources rs ON cd.resourceid=rs.resourceid
LEFT JOIN ComponentDefinition product ON rs.COMPONENTID=product.COMPONENTID LEFT JOIN ResourceLocation resLocation ON rs.RESOURCEID=resLocation.RESOURCEID
WHERE mcdt.CREATEDDATE >= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2018-08-01 00:00:00') * 1000 AS BIGINT) AND mcdt.CREATEDDATE <= CAST(EXTRACT(EPOCH FROM TIMESTAMP '2020-08-30 00:00:00') * 1000 AS BIGINT) ORDER BY 1
New to ADSelfService Plus?
Related Articles
Contract and Service Plans details - Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...Query to retrieve the active and expired CPH contracts details
Tested in: 14620, 14610 and 14306 Query 1: To return the Active CPH contracts details: select ad.org_name "Account", ad.org_name "Account", sp.serviceplanname "Service Plan", ac.CONTRACTNO "Contract No", longtodate(ac.startdate) "Contract Start ...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", ...To view the contract details to which an asset is associated through API
Tested in 14700. PURPOSE: To view the contract details through API, with the asset ID to which an asset is associated to the contract. STEPS: URL: <domain_url>/api/v3/assets/{asset_id}/contracts Method: GET Input Data: None Response: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) ...