Unable to list pending Account Plan Change Requests

Issue/Introduction:

Even though dashboard shows few account plan change requests as pending when you click on them the actual request will be missing and you will see below stack trace in catalina.out

ERROR (http-nio-37080-exec-11:) - [general] -- com.thelevel.cms.services.exceptions.ServiceException
at com.thelevel.lrs.daoservices.entity.OrganizationEntityServices.getOrganizationAdmin(OrganizationEntityServices.java:382)
at com.thelevel.lrs.services.organization.OrganizationSourceServices.getOrganizationAdmin(OrganizationSourceServices.java:200)
at com.thelevel.lrs.bm.AccountPlanRequestXS.listRequests(AccountPlanRequestXS.java:302)
at com.thelevel.lrs.bm.AccountPlanRequestXS.getLRSXML(AccountPlanRequestXS.java:134)
at com.thelevel.lrs.AbstractLRSSource.getXML(AbstractLRSSource.java:97)
at com.thelevel.cms.xmlsources.AbstractXMLSource.getXML(AbstractXMLSource.java:172)
at com.thelevel.cms.site.PageRule.handleRequest(PageRule.java:281)
at com.thelevel.cms.site.CMSSiteHandler.handleRequest(CMSSiteHandler.java:488)
at com.thelevel.cms.site.CMSSiteHandler.handleRequest(CMSSiteHandler.java:439)
at com.thelevel.cms.site.SiteServlet.processRequest(SiteServlet.java:424)
at com.thelevel.cms.site.SiteServlet.instrumentRequest(SiteServlet.java:224)
at com.thelevel.cms.site.SiteServlet.filterRequest(SiteServlet.java:178)
at com.thelevel.cms.site.SiteServlet.service(SiteServlet.java:128)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:742)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:19

Cause:

These are account plan requests for an organization that no longer exists or that the organization does not have an organization admin.

Environment:

CA Api Developer Portal 3.5

Resolution:

You can use the following SQL to identify and correct the records.

create temporary table orphaned_accountplan_requests as (select req.*, org.name from lrsaccountplanrequest req
left outer join cmsOrganization org on req.orgid = org.orgid left outer join cmsorganizationusers orguser on org.orgid = orguser.orgid left outer join cmsUserRoles ur on ur.uid = orguser.userid left outer join cmsRoles r on r.id = ur.roleId and r.name = 'organizationAdmin' left outer join cmsUsers u on orguser.userid = u.id where u.id is null and req.status = 'Requested');

update lrsaccountplanrequest set status = 'Rejected' where id in (select id from orphaned_accountplan_requests);

Once you are satisfied with the results you can remove the temporary table.

drop table orphaned_accountplan_requests;