Contract and Service Plans details - Query Report

Contract and Service Plans details - Query Report

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

          • Related Articles

          • Query to show requests with contract billing, service plan and time spent details

            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 get Asset contract and its details

            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",       ...
          • Steps to Change Report Owner

            Kindly follow the below instruction to change the report owner. ( From Tech A  to Tech B) Query to get Technician User ID:     SELECT * FROM AAAUSER WHERE FIRST_NAME LIKE '%TECH NAME%'; You may replace the Tech A name and Tech B name to get the user ...
          • Query to show contract billing details with requests

            PGSQL: ​ select ad.org_name "Account", wo.workorderid "Request ID", wo.workorderid "Request ID", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name", sp.timeperiod "Bill Cycle", cast((sum(ct.TIMESPENT)/1000 * interval '1 second') ...
          • Query to list the custom and query reports and the technician created

            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 LEFT JOIN customreport_details custrepdet ...