Billing
Query to list the request and its worklogs with the hours spent and its charges
Use case The query will list the request details with the timespent, hours consumed and its charge, Sample Table DB : Poatgres and MSSQL Query SELECT ad.ORG_NAME AS "Account", ac.contractname "Contract Name", wo.WORKORDERID AS "Request ID", ...
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 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') ...
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 ...
Consumed Units
Consumed units (Charge by Requests) Go to Reports Tab >> New Query Report >> Run the below query PGSQL & MSSQL: select ad.org_name "Account",sp.serviceplanname "Service Plan",longtodate(ac.startdate) "Start Date", longtodate(ac.expirydate) "Expiry ...
Billable and Non Billable worklogs of a request
Non Billable Requests pgsql SELECT wo.workorderid "Request ID", wo.TITLE AS "Subject", longtodate(ct.CREATEDTIME) AS "Time Spent Created Time", rctd.FIRST_NAME AS "Time Spent Technician", TO_CHAR((sum(ct.TIMESPENT)/1000 || ' second')::interval, ...