Query Report to find the Contract details in SCP 11.0
SELECT ad.ORG_NAME AS "Account", ac.CONTRACTNAME AS "Contract Name", ac.CONTRACTNO AS "Contract Number", ( case when sp.PLANTYPE like 'Charge Per Hour' then concat(cast(((bc.consumedunits/(1000))/(60*60)) as varchar), ':' ,cast((((bc.consumedunits/(1000))/60)%60) as varchar)) else cast(bc.consumedunits as varchar) end) "Allowance Consumed",sp.fixedmonthlyunits "fixedmonthlyunits", bc.TOPUPALLOWANCE , (case when sp.PLANTYPE like 'Charge Per Hour' then concat(cast((((COALESCE ((SELECT CLOSINGBALANCE FROM billhistory WHERE billhistory.contractid=ac.contractid GROUP BY billid ORDER BY billid DESC LIMIT 1 ),0 ) + (case when sp.PLANTYPE like 'Charge Per Hour' then sp.fixedmonthlyunits*3600000 else sp.fixedmonthlyunits end) -(bc.consumedunits)+(case when sp.PLANTYPE like 'Charge Per Hour' then bc.TOPUPALLOWANCE*3600000 else bc.TOPUPALLOWANCE end))/(1000))/(60*60)) as varchar), ':' ,cast(((((COALESCE ((SELECT CLOSINGBALANCE FROM billhistory WHERE billhistory.contractid=ac.contractid GROUP BY billid ORDER BY billid DESC LIMIT 1 ),0 ) + (case when sp.PLANTYPE like 'Charge Per Hour' then sp.fixedmonthlyunits*3600000 else sp.fixedmonthlyunits end) -(bc.consumedunits)+(case when sp.PLANTYPE like 'Charge Per Hour' then bc.TOPUPALLOWANCE*3600000 else bc.TOPUPALLOWANCE end))/(1000))/60)%60) as varchar)) else cast((COALESCE ((SELECT CLOSINGBALANCE FROM billhistory WHERE billhistory.contractid=ac.contractid GROUP BY billid ORDER BY billid DESC LIMIT 1 ),0 ) + (case when sp.PLANTYPE like 'Charge Per Hour' then sp.fixedmonthlyunits*3600000 else sp.fixedmonthlyunits end) -(bc.consumedunits)+(case when sp.PLANTYPE like 'Charge Per Hour' then bc.TOPUPALLOWANCE*3600000 else bc.TOPUPALLOWANCE end)) as varchar) end) "Allowance Remaining", sp.PLANTYPE FROM AccountContract ac LEFT JOIN AccountDefinition ad ON ac.ACCOUNTID=ad.ORG_ID left join billcycle bc ON ac.contractid=bc.contractid left join serviceplan sp ON ac.serviceplanid=sp.serviceplanid where sp.PLANTYPE NOT LIKE 'Fixed Charge';
Output :
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", ...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) ...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, ...