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 Date",
longtodate(ac.expirydate) "Contract Expiry Date",
sp.fixedmonthlyunits "Total Allowance (Hours)",
MAX(tph.topupunits) "Bring Forward Hours",
(MAX(bc.consumedunits)/3600000) "Hours Used",
case when tph.topupunits > 0 then MAX(sp.fixedmonthlyunits)-(MAX(bc.consumedunits)/3600000) + MAX(tph.topupunits) else MAX(sp.fixedmonthlyunits)-(MAX(bc.consumedunits)/3600000) END AS "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 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 topuphistory tph on ac.contractid=tph.contractid
where ac.isactivecontract ='true' and sp.plantype='Charge Per Hour' and ac.accountid in ($Account)
group by ad.org_name,ac.startdate,ac.expirydate,sp.serviceplanname,sp.FIXEDMONTHLYUNITS,tph.topupunits,ac.contractno order by 1
Query 2:
To return both the Active and Expired 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 Date",
longtodate(ac.expirydate) "Contract Expiry Date",
sp.fixedmonthlyunits "Total Allowance (Hours)",
MAX(tph.topupunits) "Bring Forward Hours",
(MAX(bc.consumedunits)/3600000) "Hours Used",
case when tph.topupunits > 0 then MAX(sp.fixedmonthlyunits)-(MAX(bc.consumedunits)/3600000) + MAX(tph.topupunits) else MAX(sp.fixedmonthlyunits)-(MAX(bc.consumedunits)/3600000) END AS "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 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 topuphistory tph on ac.contractid=tph.contractid
where sp.plantype='Charge Per Hour' and ac.accountid in ($Account)
group by ad.org_name,ac.startdate,ac.expirydate,sp.serviceplanname,sp.FIXEDMONTHLYUNITS,tph.topupunits,ac.contractno order by 1
New to ADSelfService Plus?
Related Articles
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 ...Can we extend the expired contract?
Only an active contract can be renewed. If the contract has expired then a new contract has to be created from the latest duration has to be created. Overlapping of dates in this scenario is not possible.Query to retrieve the requests details
Tested in: 14610, 14301 QUERY: SELECT wo.WORKORDERID AS "Request ID", LONGTODATE(wo.CREATEDTIME) AS "Created Time", LONGTODATE(wo.RESPONDEDTIME) AS "Responded Date and Time", LONGTODATE(wo.RESOLVEDTIME) AS "Resolved Date and Time", ...The requests are stricken, when associated with expired contract
When , in Request Module, the requests are stricken out, the below mentioned is the reason and solution to remove it. -> The stricken out of the tickets indicates that the contract associated with that ticket has expired. -> You can follow the below ...Query to retrieve the Change Details
Tested in: 14504, 14610 QUERY: SELECT chdt.CHANGEID AS "Change ID", cmDef.FIRST_NAME AS "Change Manager", ownaaa.FIRST_NAME AS "Change Owner", approvaldef.STATUSNAME AS "Approval Status", qd.QUEUENAME AS "Group", statusDef.STATUSDISPLAYNAME AS ...