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 Cycle",fixedmonthlycharges "Fixed Base Charge",sp.fixedmonthlyunits "Allowance", sp.fixedhourcost "Fixed Cost Per Hour",sp.operationalhourcost "OP Hrs Cost",
sp.nonoperationalhourcost "Non OP Hrs Cost", sp.weekendhourcost "Weekend Cost",sp.holidayhourcost "Holiday Cost", longtodate(ac.startdate) "Contract Start Date", longtodate(ac.expirydate) "Contract Expiry Date", ac.isactivecontract "Is Active Contract", ad.org_name "Account" from workorder wo LEFT JOIN workorderstates wos on wo.workorderid = wos.workorderid LEFT JOIN statusdefinition sd on sd.statusid = wos.statusid
left join accountsitemapping asm on asm.siteid = wo.siteid
inner join accountcontract ac on ac.accountid = asm.accountid
left join serviceplan sp ON ac.serviceplanid=sp.serviceplanid
left join billcycle bc ON ac.contractid=bc.contractid
Left join accountdefinition ad on asm.accountid = ad.org_id
where ac.accountid in ($Account)
group by ad.org_name,ac.startdate,ac.expirydate,sp.serviceplanname,sp.FIXEDMONTHLYUNITS,ac.contractno,ac.contractname,sp.plantype,ac.isactivecontract,sp.timeperiod,sp.fixedmonthlycharges,sp.fixedhourcost,sp.operationalhourcost,sp.nonoperationalhourcost,sp.weekendhourcost,sp.holidayhourcost ORDER BY 8 ASC
With billing mode as prepaid and ONLY active contracts:
select ad.org_name "Account", ac.contractname "Contract Name", ac.isactivecontract "Is Active Contract", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type",sp.fixedmonthlyunits "Total Allowance", sum(ct.timespent/3600000) "Hours Used" , (sp.fixedmonthlyunits)+COALESCE(bh.closingbalance/1000/3600,0)-(sum(ct.timespent)/3600000) "Hours Remaining" from workorder wo LEFT JOIN workorderstates wos on wo.workorderid = wos.workorderid LEFT JOIN statusdefinition sd on sd.statusid = wos.statusid
left join accountsitemapping asm on asm.siteid = wo.siteid
inner join accountcontract ac on ac.accountid = asm.accountid
left join billhistory bh on ac.contractid=bh.contractid
left join serviceplan sp ON ac.serviceplanid=sp.serviceplanid
left join billcycle bc ON ac.contractid=bc.contractid
Left join accountdefinition ad on asm.accountid = ad.org_id
left join workordertocharge wotc on wotc.workorderid=wo.workorderid
inner join chargestable ct on ct.chargeid=wotc.chargeid
left join NOBILLREQUESTCHARGES nbrc on nbrc.requestchargeid=ct.chargeid
where ac.accountid in ($Account) AND ac.isactivecontract='true' and sp.billingmode=1
group by ad.org_name,sp.serviceplanname,sp.FIXEDMONTHLYUNITS,ac.contractname,sp.plantype,ac.isactivecontract, bh.closingbalance ORDER BY 1 ASC
NOTE: ($Account) is incldued in the query. For account specific data, please select the Account at the top from the down
New to ADSelfService Plus?
Related Articles
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) ...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 ...Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...Query to show requests with contract billing, service plan and time spent details (MSSQL)
Working on : 14500 MSSQL: SELECT "wo"."WORKORDERID" AS "Request ID", "aau"."FIRST_NAME" AS "Requester", "wo"."TITLE" AS "Subject", "qd"."QUEUENAME" AS "Group", "ti"."FIRST_NAME" AS "Technician", "std"."STATUSNAME" AS "Request Status", ...Query to list the custom and query reports and the technician created (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Use case The query displays the custom reports and query reports saved by technicians. Query SELECT custrepdet.report_name "Report Name", au.first_name"Created By" from customreportquery custrep ...