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
Login Frequency Query Report
The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP ...Query to get the Request Approval details
Compatible builds : 14000 to 14200 DB : MSSQL /PGSQL OUTPUT: SELECT wo.WORKORDERID AS "Request ID", wo.title AS "Subject", wo.REQUESTERID AS "Requester ID", LONGTODATE(wo.CREATEDTIME) AS "Requested Date", LONGTODATE(apdet.sent_time) AS "Action ...Analytics - SupportCenter Plus integration PKIX path building failed (for SCP 11.0)
While syncing Analytics Reports, this error may appear where the self-signed certificate to sync both Analytics Reports and SCP will be missing. These traces (mentioned below) can be viewed in the file zrmeinteglog0.txt under the location ...Query for Support Rep list in DB
Please run this query to find the list of Support Reps available with login in the system select * from aaauser aaau join sduser sdu on aaau.user_id=sdu.userid inner join helpdeskcrew hd on sdu.userid=hd.technicianid inner join aaalogin aaal on ...query to find the unapproved contacts -> PGSQL
Below is the query to find the unapproved contacts -> PGSQL-> can be used from version 11 SELECT aaauser.User_id AS "User ID", aaauser.first_name "First name", aaauser.last_name "Last name", hd.id "Portal ID", hd.displayname "Portal Name", ...