Billing
Query to list the request and its worklogs with the hours spent and its charges (MSSQL)
Tested in build MSSQL (14306) 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", ...
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", ...
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 list the Allowance, used and remaining Hours(MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) select ad.org_name "Account",sp.serviceplanname "Service Plan",longtodate(ac.startdate) "Contract Start Date", longtodate(ac.expirydate) "Contract Expiry Date", sp.fixedmonthlyunits "Total Allowance ...
Query to find the Consumed Units (Charge by Requests / Hour) (PGSQL & MSSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) Go to Reports Tab >> New Query Report >> Run the below query Consumed units (Charge by Requests) : PGSQL & MSSQL: select ad.org_name "Account",sp.serviceplanname "Service Plan",longtodate(ac.startdate) ...
Query for Billable and Non Billable worklogs of a request (PGSQL)
Tested in Build PGSQL (14300) 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 ...