Unable to list pending Account Plan Change Requests
Article ID: 102744
Updated On:
Products
CA API Developer Portal
CA API Gateway
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
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;
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;
Feedback
Powered by
